DATEADD with milliseconds

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » DATEADD with milliseconds

DATEADD with milliseconds

Rate Topic

Display Mode

Topic Options

Author

Message

Stef Teal

Posted Thursday, February 19, 2009 8:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 04, 2009 1:49 PM
Points: 100, Visits: 100

I was hoping that the following code:

declare @end_date as datetime
set @end_date = '2008-03-10 02:00:00.000'
SELECT DATEADD(ms,-1,DATEADD(mm, DATEDIFF(m,0,@end_date)+1,0))

would give me '2008-03-31 23:59:59.999'

but it doesn't, i get '2008-04-01 00:00:00.000'

I think this is because datetime is accurate to roughly 3 milliseconds so it rounds to the next day. If I change it to -2 ms....
SELECT DATEADD(ms,-2,DATEADD(mm, DATEDIFF(m,0,@end_date)+1,0))
I correctly get '2008-03-31 23:59:59.997'

So the question is: How do I accurately determine the last day in the month for a given date, to the last millisecond (999)?

Post #660406

Lowell

Posted Thursday, February 19, 2009 8:34 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:06 PM
Points: 11,626, Visits: 27,687

I personally use the 997 milliseconds, otherwise it;'s the next day.

SET DATEFIRST 1
declare @mon datetime,
        @fri datetime    
  SELECT     
    @mon =DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) ,  
    @fri =DATEADD(ms,-2,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+ 5 ) 

Results:
Monday = 2009-02-16 00:00:00.000	
Friday = 2009-02-20 00:00:00.000	
EODFri = 2009-02-20 23:59:59.997

--so i would use thhoise dates for a BETWEEN command:
SELECT...WHERE  SOMEDATE between @mon and @fri

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #660423

Luke L

Posted Thursday, February 19, 2009 8:38 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 2,891, Visits: 5,858

A lot of it depends on what you are trying to do. If you are accepting dates without time from your users and or are passing them in yourself,
Instead using "columnname between @Start_Date and @EndDate ", You could use columnName >= @Start_Date AND columnName < datetime(d,1,@EndDate)
For the month of January you end up with >= '2009-01-01 00:00:00.000 and < '2009-02-01 00:00:00.000'

-Luke.

Edited to correct things the page stripped out...

To help us help you read this

For better help with performance problems please read this

Post #660433

« Prev Topic | Next Topic »

Permissions