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.
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
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: