Truncate Hour, Minute, Second and MilliSecond in SQL Server

Here’s how to Truncate a DateTime in SQL Server

SELECT GETDATE() as CurrentDateTime;
SELECT DATEADD(day,DATEDIFF(day,0,GETDATE()),0) as [Truncate-HrMinSecMilliSec];
SELECT DATEADD(hour,DATEDIFF(hour,0,GETDATE()),0) as [Truncate-MinSecMilliSec];
SELECT DATEADD(minute,DATEDIFF(minute,0,GETDATE()),0) as [Truncate-SecMilliSec];

Now when I did the same for seconds using

SELECT DATEADD(second,DATEDIFF(second,0,GETDATE()),0);

I got the error

Msg 535, Level 16, State 0, Line 1

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

To resolve the error, do the following:

SELECT DATEADD(second,DATEDIFF(second, '2010-10-01',GETDATE()),'2010-10-01') as [Truncate-MilliSec];

Here’s the Output after running all the queries

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: