Update time value of datetime field type

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update time value of datetime field type

Author

Topic

bogers24
Starting Member

1 Post

Posted - 2009-06-18 : 13:46:18

I am looking for the best method to update the time value of a datetime field type. This is just a small sample of data. My table has 10,000+ rows.

Example table data:
Fields: dataid, EndDate
1, 06/01/2009 12:00:00 AM
2, 06/02/2009 12:00:00 AM
3, 06/05/2009 04:00:00 AM
4, 06/06/2009 12:00:00 AM
5, 06/07/2009 04:00:00 AM

I would like to change the time for all records to be 04:00:00 AM.
1, 06/01/2009 04:00:00 AM
2, 06/02/2009 04:00:00 AM
3, 06/05/2009 04:00:00 AM
4, 06/06/2009 04:00:00 AM
5, 06/07/2009 04:00:00 AM

Any guidance would be much appreciated. Thanks!

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-18 : 13:58:15

[code]SELECT DATEADD(hh,4,DATEADD(dd,DATEDIFF(dd,0,EndDate),0))[/code]

Some days you're the dog, and some days you're the fire hydrant.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-06-18 : 14:02:29

[code]
update MyTable
set
EndDate = dateadd(dd,datediff(dd,0,EndDate),'04:00:00.000')
[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources