GetDate() not using dates correctly?

Question

I have the below SQL query:

SELECT CONVERT(varchar, st_duedate, 103) as 'Due Date',
    CASE 
        WHEN st_duedate < GETDATE() THEN 'Overdue'
        WHEN st_duedate = GETDATE() THEN 'Due Today'
        WHEN DATEDIFF(day,st_duedate,getdate()) <= 7 THEN 'Due within 7 Days'
        WHEN DATEDIFF(day,st_duedate,getdate()) > 7 THEN 'Due after 7 Days'
    END AS 'Due when'
FROM st_1

However, st_duedate dates that are dated today are coming up as 'Overdue' and anything after today comes up as 'Due within 7 days' even if the date is months away.

Is it something to do with the converting of the date? I've just tried having st_duedate as the column name instead of converting but still have the same issue.

Any help would be highly appreciated!

The problem is that you're using `CONVERT(varchar, st_duedate, 103)` to get formatted string instead of proper date/datetime. — Maciej Los, Apr 21 '21 at 07:47
@MaciejLos if I remove the conversion I am still getting the same issue — PeterK, Apr 21 '21 at 07:53
If I remove the format the time is all 00:00:00.000 which I presume will affect the "due today" issue but then the dates months away still has "due within 7 days"? — PeterK, Apr 21 '21 at 07:55
It appears that you only need the "date" part of the `datetime` value. Perhaps this will help: [How to return only the Date from a SQL Server DateTime datatype](https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-datatype) — Abra, Apr 21 '21 at 07:58
`DATEDIFF(day,st_duedate,getdate())` is always going to return a *negative* result if `st_duedate` is in the future. I think you've got that logic the wrong way around. — Damien_The_Unbeliever, Apr 21 '21 at 08:03

score 3 · Accepted Answer · answered Apr 21 '21 at 08:09

I would suggest moving the DATEDIFF calculation into a subquery so that you're just working with one integer value to consider cases for^1,2:

SELECT CONVERT(varchar, st_duedate, 103) as 'Due Date',
    CASE 
        WHEN daysDiff > 0 THEN 'Overdue'
        WHEN daysDiff = 0 THEN 'Due Today'
        WHEN daysDiff >= -7 THEN 'Due within 7 Days'
        ELSE 'Due after 7 Days'
    END AS 'Due when'
FROM (select st_duedate, DATEDIFF(day,st_duedate,getdate()) as DaysDiff from st_1) x

You'll notice I've also simplified the last expression in the CASE since we seem to have exhausted the possibilities

¹Your existing code switched from direct comparisons to using DATEDIFF part way through and ended up "reversing the sign" of the logic which is why the last two cases weren't working as expected.

²Also using just DATEDIFF logic with day automatically ignores any time components of the datetime values, which was the other main problem here.

GetDate() not using dates correctly?

1 Answers1