137

I have a query to fetch date diff between 2 datetime as :

SELECT DATEDIFF(DAY, @CreatedDate , GETDATE())

Ex :

SELECT DATEDIFF(DAY, '2013-03-13 00:00:00.000' , GETDATE())

I need to have a query work like this which will subtract a day from created day:

SELECT DATEDIFF(DAY, **@CreatedDate- 1** , GETDATE())
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
James
  • 2,136
  • 3
  • 23
  • 42
  • 1
    Why not just add 1 to the result? – Damien_The_Unbeliever Mar 26 '13 at 08:01
  • If *subtracting* 1 from the result is giving the desired answer, then the question you've posed seems wrong, since *subtracting* from the result is equivalent to *adding* a number of days onto the earlier of the dates. – Damien_The_Unbeliever Mar 26 '13 at 08:08
  • Then the expression you posted as your first reply to me is the one that I'd use - you could post it as an answer, but as I say, it means that your question isn't really correct (the `DATEDIFF` between `2003-03-12` and today is 14, not 12). – Damien_The_Unbeliever Mar 26 '13 at 08:15
  • Yes..you are right..it works like the opposite with the results.. – James Mar 26 '13 at 08:59

9 Answers9

149

Try this

SELECT DATEDIFF(DAY,  DATEADD(day, -1, '2013-03-13 00:00:00.000'), GETDATE())

OR

SELECT DATEDIFF(DAY,  DATEADD(day, -1, @CreatedDate), GETDATE())
yogi
  • 19,175
  • 13
  • 62
  • 92
  • 4
    Following the answer from Philip Rego, you can use SELECT GETDATE() - 1 to subtract days from a date. – Jose Barbosa Jul 23 '19 at 16:07
  • 3
    Be slightly careful with the minus 1. As long as everyone knows that it only works on DateTime. It does NOT work on Date data types. CAST(GetDate() - 1 AS DATE) /* works */, CAST(GetDate() AS DATE) - 1 /* error */ – Code Novice May 12 '22 at 14:32
57

I am not certain about what precisely you are trying to do, but I think this SQL function will help you:

SELECT DATEADD(day,-1,'2013-04-01 16:25:00.250')

The above will give you 2013-03-31 16:25:00.250.

It takes you back exactly one day and works on any standard date-time or date format.

Try running this command and see if it gives you what you are looking for:

SELECT DATEADD(day,-1,@CreatedDate)
Aboutblank
  • 697
  • 3
  • 14
  • 31
Chris
  • 571
  • 4
  • 2
54

To simply subtract one day from todays date:

Select DATEADD(day,-1,GETDATE())

(original post used -7 and was incorrect)

SamG
  • 815
  • 8
  • 12
FoxDeploy
  • 12,569
  • 2
  • 33
  • 48
38

Apparently you can subtract the number of days you want from a datetime.

SELECT GETDATE() - 1

2016-12-25 15:24:50.403
Philip Rego
  • 552
  • 4
  • 20
  • 35
  • 1
    Postgres equivalent would be `SELECT CURRENT_DATE - 1` or `SELECT CURRENT_DATE - INT '1'`. More [here](https://www.postgresql.org/docs/8.2/functions-datetime.html) – chriszo111 Sep 13 '21 at 13:14
11

This should work.

select DATEADD(day, -1, convert(date, GETDATE()))
samithagun
  • 664
  • 11
  • 25
1
SELECT DATEDIFF (
    DAY, 
    DATEDIFF(DAY, @CreatedDate, -1), 
    GETDATE())
Daniel Imms
  • 47,944
  • 19
  • 150
  • 166
1

Try this, may this will help you

SELECT DATEDIFF(DAY, DATEADD(DAY,-1,'2013-03-13 00:00:00.000') , GETDATE())
Vijay Singh Rana
  • 1,060
  • 14
  • 32
0

To be honest I just use:

select convert(nvarchar(max), GETDATE(), 112)

which gives YYYYMMDD and minus one from it.

Or more correctly 

select convert(nvarchar(max), GETDATE(), 112) - 1

for yesterdays date.

Replace Getdate() with your value OrderDate

select convert(nvarchar (max),OrderDate,112)-1 AS SubtractDate FROM Orders

should do it.

Irshad
  • 3,071
  • 5
  • 30
  • 51
meekon5
  • 9
  • 2
  • It doesn't work if any cases, for example if you replace the getdate() by '2022-11-01 10:00:00.000' your result will be '20221100' instead of '20221031' – Huojian Nov 21 '22 at 13:31
-3

You can try this.

Timestamp=2008-11-11 13:23:44.657;

SELECT DATE_SUB(OrderDate,INTERVAL 1 DAY) AS SubtractDate FROM Orders

output :2008-11-10 13:23:44.657

I hope, it will help to solve your problem.

chintan
  • 471
  • 3
  • 16