Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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?
0
feesu
Asked:
feesu
1 Solution
 
Guy Hengel [angelIII / a3]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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now