Subtract one day from datetime

How to rock date subtraction in different scenarios

Subtracting a day from user-defined variables

Think of dates as apples. If you have 10 apples (@CreatedDate), and you give one away 🔽:

DECLARE @CreatedDate DATETIME = '2023-01-15 08:00:00';
SELECT DATEADD(day, -1, @CreatedDate);


This gives you the datetime 24 hours earlier. You'll be left with 9 apples 🍎.

Handling the unforgiving end-of-month dilemma

Will DATEADD subtract into the abyss at the end of a month? Fear not:

SELECT DATEADD(day, -1, '2023-03-01');


SQL serves leap years and month boundaries like an experienced bartender. No "month calculations" hangover!

Dancing with the TIME part of datetime

The DATEADD function brings everything, including the kitchen sink (the time component). But if you want your datetime neat, stir and serve:

SELECT CAST(DATEADD(day, -1, @CreatedDate) AS DATE);


This cocktail pours out the result as a date, kicking out the time gang.

Making it work across a world of databases

You'll find DATEADD or its relatives useful in SQL land. But, proceed with caution. Here are a few bumps to look out for:

In Oracle SQL

Oracle treats dates like numbers. Like a subtract ninja, we just go:

SELECT SYSDATE - 1 FROM DUAL;


This is like asking, "What's 10 minus 1?" So easy even a ninja baby can do it!

Handling daylight saving time (DST)

With the irregular time shifts, a day might look like 23 or 25 hours. Time travel isn't always as smooth as sci-fi makes it feel!

Subtracting a dynamic number of days

When you want to go back variable days like a time-travel amateur, DATEADD can still be your reliable TARDIS:

DECLARE @DaysToSubtract INT = 1;
SELECT DATEADD(day, -@DaysToSubtract, GETDATE());


This works whether you're whipping up a quick DeLorean trip or plotting a full-on Quantum Realm journey.

Watch out for the leap year

Another friendly reminder from your local time travel service — DATEADD accounts for February 29th. We've got leap years covered so just sit back, relax and enjoy your ride through time.

Demystifying date functions

A sound knowledge of how date functions work shields you from costly mistakes. Here's a double take:

DATEDIFF vs. DATEADD

DATEDIFF doesn't subtract days, but finds the difference between two dates. On the other hand, DATEADD adjusts a date by an interval. Don't get them twisted!

-- DATEDIFF: Calculates the difference between two dates in days
SELECT DATEDIFF(day, '2023-01-14', '2023-01-15');  -- Outputs: 1 (I kinda miss December)

-- DATEADD: Deducts one day from a specific date
SELECT DATEADD(day, -1, '2023-01-15');  -- Outputs: '2023-01-14' (Bazinga! Lost a day)


Verifying the accuracy of your date manipulation

Trust only the DATEADD function, but verify the result. This helps ensure you meet the business requirements when running a DeLorean time machine.

Playing safe for precision and accuracy

DATEADD turns back the clock by exactly 24 hours, regardless of what your watch says. Try these on for size:

• Use GETUTCDATE() instead of GETDATE() when you need the current UTC date and time. Timezones – punisher of tardy folks.
• If pinpoint accuracy is crucial, secure your datetime variable at the intended starting point. Precision – a perfectionist's best friend.
• Always validate the correctness of your date subtraction. Calendars are old school but they got your back!