How to subtract 30 days from the current date using SQL Server

Processing different data types

Transforming varchar to datetime

To navigate from varchar to datetime, before heading towards date manipulation, use CAST or CONVERT:

-- Who says you can't time travel? Here we go, back 30 days!
SELECT DATEADD(day, -30, CAST(varchar_date_column AS datetime))
FROM your_table;


Or, with CONVERT:

-- Let's spin the disc (like a DJ!) backwards by 30 days
SELECT DATEADD(day, -30, CONVERT(datetime, varchar_date_column, 120))
FROM your_table;


Note: The style 120 gives us the yyyy-mm-dd hh:mi:ss (24-hour) format.

Subtracting days from datetime columns

For datetime columns, as easy as pie:

-- Sleeve-rolling time: roll back 30 days
SELECT DATEADD(day, -30, datetime_column)
FROM your_table;


Advanced cases unravelled

Dealing with a range in days

DATEADD is super-flexible, capable of manipulating different intervals like hours, months, or years:

SELECT DATEADD(month, -1, GETDATE()); -- Subtract 1 month 
SELECT DATEADD(year, -1, GETDATE());  -- Subtract 1 year
SELECT DATEADD(hour, -720, GETDATE()); -- Subtract 720 hours (aka 30 days, just for fun 😁)


Leap year magic

Gotcha! Leap years can add an extra layer of complexity. When subtracting months or years, your result can take a detour:

-- Subtract 1 year from February 29, 2020 
SELECT DATEADD(year, -1, '20200229'); // Result: 20190228 (Because 2019 is greedy, stole one day 😂😂)


Addressing timezones

Time-zones giving a headache? Use AT TIME ZONE to ease the pain:

SELECT DATEADD(day, -30, GETDATE() AT TIME ZONE 'Central European Standard Time');


Common pitfalls

Misplaced data types

Avoid storing date-time values in varchar. Always choose a date-specific data type - it makes calculations accurate, and life a lot simpler.

Overlooking DST

Watch out for Daylight Saving Time - it may sneakily introduce hour changes. Beware if you're dealing with exact hour calculations.

Unnecessary conversions

DATEADD is an efficient function, but repeatedly converting varchar to datetime can affect performance. Thus, upgrade your data types in the database when time-conscious computations are common.