Getdate(), -1 day

Question

I do not understand why, but somehow this query doesn't work. I want to take system date -1 day where the sysdate is smaller by 1 day then current date.

WHERE
    a.SEND_Date >= dateadd(DD,-1,(CAST(getdate() as date) as datetime)) 

Guenna

What does "doesn't work" look like? – Forefinger

Do you actually need the CAST there? – Biographical

I am not sure if that CAST is necessary. That's why I am asking help to make this better or fix this query line. – Guenna

I figured that maybe this way is even better DATEADD(day,DATEDIFF(day,-1,GETDATE()),-1) – Guenna

Answer 1

The CAST depends on what kind of date type you need. If you need only to compare dates you can use only:

dateadd(DD, -1, cast(getdate() as date))

If you need to compare with date time you can use:

dateadd(DD,-1,getdate())

That wil give you datetime like this: 2016-01-11 10:43:57.443

Lustrate

Answer 2

In T-SQL (sqlserver) you can simply do :

getDate()-1

The function substracts (days) as standard.

Snowbird

This will be a DATETIME value, with hours and minutes, not a DATE value which is just year/month/day. – Shigella

Answer 3

Or you can try this without making it any more difficult?

 CAST(GETDATE()-1 as date )

Narbonne

Answer 4

The checked answer still has time (00:00:00), in my version anyway. To get DATE only use: Select Convert(date,dateadd(day, -1, getdate()))

Both have same weight, 0.001 seconds

Patch

Answer 5

There's just one CAST missing:

dateadd(DD,-1,(CAST(getdate() as date) as datetime))

two times "as" (as date + as datetime) but only one time "CAST" => something wrong - should be:

dateadd(DD,-1,CAST(CAST(getdate() as date) as datetime))

Fikes

Answer 6

Please Don't follow any as this date will come issue on 01/05/2020 will print as 00/05/2020. So kindly use this below for the fix

select CONVERT(varchar, DATEADD(DAY, -1, convert(Nvarchar, GETDATE(),112)),112)

Epistemic