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

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. — N2hvits, Jan 12 '16 at 08:39
I figured that maybe this way is even better DATEADD(day,DATEDIFF(day,-1,GETDATE()),-1) — N2hvits, Jan 12 '16 at 08:40

score 41 · Accepted Answer · edited Jan 12 '16 at 09:09

41

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

edited Jan 12 '16 at 09:09

Andy K

4,944
10
53
82

answered Jan 12 '16 at 08:49

Yahel

546
5
6

score 14 · Answer 2 · edited Oct 29 '19 at 15:05

14

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

getDate()-1

The function substracts (days) as standard.

edited Oct 29 '19 at 15:05

Obsidian

3,719
8
17
30

answered Oct 29 '19 at 13:31

FSciacca

174
1
5

This will be a DATETIME value, with hours and minutes, not a DATE value which is just year/month/day. – Ross Presser Oct 29 '19 at 13:40

score 7 · Answer 3 · answered Apr 10 '20 at 11:19

7

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

 CAST(GETDATE()-1 as date )

answered Apr 10 '20 at 11:19

Ivan Ivanov

131
1
2
6

score 0 · Answer 4 · answered May 11 '17 at 14:20

0

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

answered May 11 '17 at 14:20

user3806549

1,428
1
17
25

score 0 · Answer 5 · edited Feb 21 '19 at 19:55

0

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

edited Feb 21 '19 at 19:55

Jérôme

1,254
2
20
25

answered Feb 21 '19 at 16:40

rf0806

1

score 0 · Answer 6 · edited May 14 '20 at 18:57

0

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)

edited May 14 '20 at 18:57

Paul Roub

36,322
27
84
93

answered May 14 '20 at 18:48

Aravind

1

Getdate(), -1 day

6 Answers6

Linked