Solved

SQL Server 2005 - Updating date for a datetime column but keeping the time

Posted on 2011-03-24
4
209 Views
Last Modified: 2012-08-14
Hi Experts,

I have a datetime column that has the same date for all rows but with different time. The column value of the first row looks like this 22/03/2011 8:00:00 AM

I want to change the date to 01/01/1900 without changing the time, how can I do that?
0
Comment
Question by:feesu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35206565
update yourtable
  set yourfield = dateadd(day, datediff(day, yourfield, 0 ), yourfield)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35206586
select convert( datetime, convert(float, myDateColumn ) - convert(int, myDateColumn ))
from myTable
0
 
LVL 18

Expert Comment

by:deighton
ID: 35208093
update YourTable
 SET YourField = CAST(CAST('01-01-1900' AS float) + CAST(YourField as Float) - FLOOR( CAST(YourField as Float)) AS DATETIME)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35214701
angelIII,

Good one.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question