Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

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:

DECLARE @today SMALLDATETIME;

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

SELECT ... FROM ...
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
1  
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: sqlperformance.com/2012/09/t-sql-queries/… –  Aaron Bertrand Sep 6 '12 at 3:36
add comment

Your Answer

 
discard

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.