Sql-server – how to get last date of today in datetime datatype

datetimesql serversql-server-2005sql-server-2008t-sql

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!!!

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

Related Solutions

Sql-server – Datetime column vs Date + Time columns

I would use DATETIME2 as opposed to DATETIME for any new development work at this point. If you don't need sub-second granularity, use DATETIME2(0) and you'll see some space savings as well.

From here I would start experimenting. If performance is poor just querying off the single column, I'd then try adding persisted computed columns to separate out the date and time components. This will ensure that the separated DATE and TIME columns will always be the same as the "master" DATETIME2(n) column.

SQL Server – Using DAY/WEEK/MONTH/YEAR Constants in Stored Procedures

This assumes that your week starts on a Sunday, and that you only have data from the past. If you need to collect data for this week/month/year/today and exclude data from the future, you need to calculate a start and end range instead of a single cutoff.

CREATE PROCEDURE dbo.GetHighScores -- always use schema prefix!
  @scope VARCHAR(5) -- why an int?
AS
BEGIN
  SET NOCOUNT ON; -- always use this!

  DECLARE @d DATETIME, @cutoff DATETIME;
  SET @d = DATEDIFF(DAY, 0, GETDATE());

  SET @cutoff = DATEADD(DAY, 1-CASE UPPER (@scope) 
    WHEN 'MONTH' THEN DAY(@d)
    WHEN 'WEEK'  THEN DATEPART(WEEKDAY, @d)
    WHEN 'YEAR'  THEN DATEPART(DAYOFYEAR, @d)
    WHEN 'DAY'   THEN 1 END, @d);

  SELECT columns -- never use SELECT * in production code!
    FROM Honor.Scores
    WHERE ScoredAt >= @cutoff; -- now you have a chance to use an index
END
GO

Some further reading you may find useful:

Best Answer

Related Solutions

Sql-server – Datetime column vs Date + Time columns

SQL Server – Using DAY/WEEK/MONTH/YEAR Constants in Stored Procedures

Related Question