InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 10:20:18 GMT20http://www.sqlservercentral.com/Forums/Topic660406-338-1.aspxA 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...Thu, 19 Feb 2009 08:38:28 GMTLuke Lhttp://www.sqlservercentral.com/Forums/Topic660406-338-1.aspxI personally use the 997 milliseconds, otherwise it;'s the next day.[code]SET DATEFIRST 1declare @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[/code]Thu, 19 Feb 2009 08:34:49 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic660406-338-1.aspxI was hoping that the following code:declare @end_date as datetimeset @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)?Thu, 19 Feb 2009 08:26:05 GMTStef Teal