Updating only time portion of datetime

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

SQL Server 2005 Forums

Transact-SQL (2005)

Updating only time portion of datetime

Author

Topic

LacOniC
Starting Member

29 Posts

Posted - 2007-09-28 : 03:07:51

Hello There.

I want to update only time portion of a datetime column as 00:00:00:000

Values are like:

2006-08-28 17:10:10.607
2007-02-10 11:24:12.090
2007-02-10 11:24:14.967

I want to do them like:

2006-08-28 00:00:00:000
2007-02-10 00:00:00:000
2007-02-10 00:00:00:000

Any suggestion?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 03:11:49

Dont update. Use it in Select

Select datecol,dateadd(day,datediff(day,0,datecol),0) from table

Madhivanan

Failing to plan is Planning to fail

LacOniC
Starting Member

29 Posts

Posted - 2007-09-28 : 03:36:47

quote:
Originally posted by madhivanan

Dont update. Use it in Select

Select datecol,dateadd(day,datediff(day,0,datecol),0) from table

Madhivanan

Failing to plan is Planning to fail

Thank you but i need to update time.

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 03:39:25

The update using the forumla that Madhi posted:


UPDATE MyTable
SET datecol = dateadd(day, datediff(day, 0, datecol), 0)

and that will set the TIME to 00:00:00 on the datecol on all rows

Backup first!

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 03:44:54

This will work too (due to implicit conversion)

UPDATE MyTable
SET datecol = datediff(day, 0, datecol)

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 03:48:21

Its a bit Grubby for production code though, isn't it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 04:05:47

quote:
Originally posted by Peso

This will work too (due to implicit conversion)

UPDATE MyTable
SET datecol = datediff(day, 0, datecol)

E 12°55'05.25"
N 56°04'39.16"

Make sure it works in all versions

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources