Converting milliseconds to 0 - SQL Server Forums

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

Converting milliseconds to 0

Author

Topic

dmarsden
Starting Member

13 Posts

Posted - 2012-03-14 : 16:13:51

Hi. Our application can't handle datetime fields with milliseconds when updating records. I need to set the milliseconds on these fields to 000. For example, 2012-03-02 15:44:13.210 needs to be updated to 2012-03-02 15:44:13.000. Any help would really be appreciated. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 16:34:26

update table
set datevalue= dateadd(ss,datediff(ss,0,datevalue),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-14 : 16:37:57

quote:
Originally posted by visakh16

update table
set datevalue= dateadd(ss,datediff(ss,0,datevalue),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I'm pretty sure that'll produce a overflow error.

Here is one option though:

SELECT CAST(CAST(CURRENT_TIMESTAMP AS DATETIME2(0)) AS DATETIME)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-14 : 16:58:10

A tweak to Visakh's code:

UPDATE table SET datevalue=DATEADD(ms, -DATEPART(ms,datevalue), datevalue)

dmarsden
Starting Member

13 Posts

Posted - 2012-03-14 : 17:13:43

Thanks! That did it. Have a good one!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources