DATEADD with milliseconds

• Stef Teal

  SSCrazy

  Points: 2382

  More actions

  February 19, 2009 at 8:26 am

  #126498

  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)?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 19, 2009 at 8:34 am

  #946771

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Luke L

  SSC-Dedicated

  Points: 35547

  More actions

  February 19, 2009 at 8:38 am

  #946781

  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[/url]For better help with performance problems please read this[/url]