Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

select dateadd(s, -1, dateadd(month, datediff(month, 0, getdate())+1,0))

when i execute this query, it gives '2012-01-30 23:59:59.000'. I donno how to get it as '2012-01-30 23:59:59.997'. If anybody Know, please say.

thanking you!!!

share|improve this question
s is for seconds. ms is for milliseconds: DATEADD() function. – ypercubeᵀᴹ Sep 5 '12 at 11:15
Use. Lower. Boundaries. – Jon Seigel Sep 5 '12 at 15:39
thank u jon seigel – cgsabari Sep 6 '12 at 10:30

Why on earth do you need the last possible value of today? Stop using BETWEEN; it is much easier (and much more reliable, and consistent across various date/time data types) to use an open ended range:


SET @today = DATEADD(DAY, DATEDIFF(DAY, '20000101', GETDATE()), '20000101');

WHERE date_column >= @today
AND date_column < DATEADD(DAY, 1, @today);

The problem with what you're doing is that the data type can affect what happens. If the underlying data type is SMALLDATETIME, it rounds up; if the underlying data type is DATETIME or DATETIME2, subtracting a second is not going to capture all of the data from that day. This is actually one of my favorite slides in my T-SQL : Bad Habits and Best Practices talk. For a lot more details, please read these blog posts:

What do BETWEEN and the devil have in common?

Bad Habits to Kick : mis-handling date / range queries

share|improve this answer
Agreed. And another option is to use WHERE CAST(date_column AS DATE) = CAST(GETDATE() AS DATE), which has nice hooks to ensure index use. – Rob Farley Sep 5 '12 at 11:57
Thanks for the information on BETWEEN and dates, Aaron. Learn something new everyday in the land of SQL. – datagod Sep 5 '12 at 12:09
I also blogged about some performance impacts of the various approaches today:… – Aaron Bertrand Sep 6 '12 at 3:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.