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

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?
feesuAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
update yourtable
  set yourfield = dateadd(day, datediff(day, yourfield, 0 ), yourfield)
0
 
knightEknightCommented:
select convert( datetime, convert(float, myDateColumn ) - convert(int, myDateColumn ))
from myTable
0
 
deightonCommented:
update YourTable
 SET YourField = CAST(CAST('01-01-1900' AS float) + CAST(YourField as Float) - FLOOR( CAST(YourField as Float)) AS DATETIME)
0
 
Anthony PerkinsCommented:
angelIII,

Good one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.