Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

DATEADD with milliseconds Expand / Collapse
Posted Thursday, February 19, 2009 8:26 AM


Group: General Forum Members
Last Login: Thursday, June 4, 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
Posted Thursday, February 19, 2009 8:34 AM



Group: General Forum Members
Last Login: Yesterday @ 1:44 PM
Points: 13,714, Visits: 35,100
I personally use the 997 milliseconds, otherwise it;'s the next day.
declare @mon datetime,
@fri datetime
@mon =DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) ,
@fri =DATEADD(ms,-2,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+ 5 )

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


--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
Posted Thursday, February 19, 2009 8:38 AM



Group: General Forum Members
Last Login: Friday, August 21, 2015 12:50 PM
Points: 2,659, Visits: 6,053
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'


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 »

Add to briefcase

Permissions Expand / Collapse