Sql-server – Can somebody please tell what is the difference between these two date comparisons

datedate formatdatetimesql server

So I have 2 queries as follows:

f.DATE >= (GETDATE() - 3)

f.DATE >= CAST(CONVERT(VARCHAR(10),GETDATE() - 3,101) AS DATETIME)

I think they are the same but the second one gives me more rows than the first one does.
The data type of f.DATE is date though.

Can you please tell me where I get it wrong? Thank you much!!

Best Answer

The SQL Server GETDATE() function returns the exact date and time as it at the time of execution including the seconds/milisecond i.e. 2020-07-24 14:08:40.670. When the query executes, the

GETDATE()-3

evaluates the date/time as it at at the time of execution minus 3 days i.e. 2020-07-21 14:08:40.670 and thus only items with an f.Date greater then/equal to 2020-07-21 14:08:40.670 will be returned.

Using CONVERT with a Style of 101 on GETDATE() returns a date in the format of mm/dd/yyyy. In your example above of

CAST(CONVERT(VARCHAR(10),GETDATE() - 3,101) AS DATETIME)

the

CONVERT(VARCHAR(10),GETDATE() - 3,101)

evaluates the date/time as it at at the time of execution minus 3 days without any time portion i.e. 2020-07-21. When this is then CAST to a datetime the time portion is defaulted to 00:00:00 i.e. 2020-07-21 00:00:00.000.

As the derived expression in your second statement is earlier than that in the first you may have additional rows returned.

Related Solutions

SQL Server – Fixing Varchar to Datetime Conversion Out-of-Range Error

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format.

Try this:

select count(*) 
  from dbo.profile 
  where [created] between convert(datetime,'2014-11-01T00:00:00.000') 
                      and convert(datetime,'2014-11-30T23:59:59.997');

Querying using dates and/or datetimes can be tricky, to make sure you are getting what you are looking for I recommend reading:

Aaron Bertrand's Bad habits to kick : mis-handling date / range queries
Robyn Page's SQL Server DATE/TIME Workbench

edit: to clarify the out of range value in your error message would be from interpreting the month as 30 and the day as 11.

SQL Server Date Format – Why Update Statements Fail with Datetime

You could deal with the issue by specifying the style and doing an explicit CONVERT() from string to datetime rather than an implicit conversion. https://msdn.microsoft.com/en-us/library/ms187928.aspx

UPDATE dbo.t SET mydatetime = CONVERT('2011-12-25 07:00:00.000', 121)

Best Answer

Related Solutions

SQL Server – Fixing Varchar to Datetime Conversion Out-of-Range Error

SQL Server Date Format – Why Update Statements Fail with Datetime

Related Question