0

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

  • 1
    Just look at the result of each: dbfiddle.uk/… Is f.date maybe a datetime rather than a date? – a_horse_with_no_name Jul 24 at 13:09
  • @a_horse_with_no_name the data type of f.date is "date". it confused me though. – mameo Jul 24 at 13:14
1

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.

| improve this answer | |
  • just run this to see the difference: select GETDATE() - 3,CAST(CONVERT(VARCHAR(10),GETDATE() - 3,101) AS DATETIME) – Bob Klimes Jul 24 at 13:45
1

You shouldn't use either of these forms. This - 3 shorthand is a timebomb waiting to happen (see this post and this video), and converting to a string and back is one of the least efficient ways to strip the time from a datetime type (see this post).

If the column is date, you should explicitly subtract 3 days from today at midnight, which can be obtained by simply converting "now" to a date type:

WHERE f.DATE >= DATEADD(DAY, -3, CONVERT(date, GETDATE()));

As an aside, you also should avoid using reserved words (like DATE) as column names.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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