CODEWITHSUNDEEP
sqlsql-serverdatetime
James
James

Reputation: 2246

Subtract one day from datetime

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

Upvotes: 148

Views: 666370

Answers (10)

tunnelview
tunnelview

Reputation: 47

This can be tricky.I have faced problems with this myself. Note that if you are trying to use this in your where clause or on your join condition, then without converting the field to a date field from a datetime, it might not fetch you any results. For example, the code snippet below doesn't give no results, even though the query at a surface level looks alright.

SELECT 
PatientID,
count(1) AS Total_Count
FROM EmergencyDepart_Data ED_Data
WHERE ED_Data.[Arrival Date] = getdate() - 1
GROUP BY PatientID

Where as the below query fetches desired results in my case when converted into date from datetime field. Here is the same code with conversion.

SELECT 
PatientID,
COUNT(1) AS Total_Count
FROM EmergencyDepart_Data ED_Data
WHERE CONVERT(DATE, ED_Data.[Arrival Date]) = CONVERT(DATE, GetDate() - 1)
GROUP BY PatientID

When I ran both queries is that there is no date that evaluates to the comparison in the where clause in the first query, therefore, the second query gives results when used after converting to date from datetime as it matches with the date that is available in the database.

For more clarity, try running the following queries.

select getdate() as Today_Date
select convert(date, getdate()) as Converted_Date

or

select getdate()-1 as Yesterday_date, getdate() as TodayDate
select convert(date, getdate()-1) Converted_Date

Now, clearly we won't have any record that matches with the time that the where clause evaluates to within the table.

Upvotes: 0

chintan
chintan

Reputation: 471

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.

Upvotes: -3

samithagun
samithagun

Reputation: 693

This should work.

select DATEADD(day, -1, convert(date, GETDATE()))

Upvotes: 12

Philip Rego
Philip Rego

Reputation: 648

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

SELECT GETDATE() - 1

2016-12-25 15:24:50.403

Upvotes: 42

FoxDeploy
FoxDeploy

Reputation: 13537

To simply subtract one day from todays date:

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

(original post used -7 and was incorrect)

Upvotes: 62

meekon5
meekon5

Reputation: 9

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.

Upvotes: 0

Chris
Chris

Reputation: 631

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)

Upvotes: 63

yogi
yogi

Reputation: 19591

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

Upvotes: 161

Vijay Singh Rana
Vijay Singh Rana

Reputation: 1090

Try this, may this will help you

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

Upvotes: 1

Daniel Imms
Daniel Imms

Reputation: 50149

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

Upvotes: 1

Related Questions