[sql] 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())

This question is related to sql sql-server datetime

The answer is


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.


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)

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.


Try this, may this will help you

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

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

SELECT GETDATE() - 1

2016-12-25 15:24:50.403

To simply subtract one day from todays date:

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

(original post used -7 and was incorrect)


This should work.

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

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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to datetime

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")?