MS SQL - Manipulate dates

By xngo on September 3, 2019

MS SQL server provides different functions to make it easier to manipulate dates. In this post, I will show you how to edit, add, subtract and get difference between dates.

Get and set dates

-- Get current date and time.
SELECT CURRENT_TIMESTAMP    -- 2019-09-04 13:22:49.693
SELECT GETDATE()            -- 2019-09-04 13:22:49.693
 
-- Convert string to DATETIME data type.
SELECT CAST('2009-05-25' AS DATE)       -- 2009-05-25
SELECT CAST('2009-05-25' AS DATETIME)   -- 2009-05-25 00:00:00.000

Add / subtract time from date

MS SQL server provides DATEADD(datepart, number, date) to add or subtract time from date. It will add or subtract according to the datepart that you specified.

-- Add time.
SELECT DATEADD(day, 1, GETDATE())   -- Tomorrow: 2019-09-05 13:22:30.677
SELECT DATEADD(month, 2, GETDATE()) -- In 2 months: 2019-09-05 13:22:30.677
SELECT DATEADD(year, 3, GETDATE())  -- In 3 years: 2021-09-04 13:22:30.677
 
-- Subtract time.
SELECT DATEADD(hour, -1, GETDATE())     -- An hour ago: 2019-09-04 12:40:33.870
SELECT DATEADD(minute, -2, GETDATE())  -- Two mins ago: 2019-09-04 13:38:33.870
SELECT DATEADD(second, -3, GETDATE()) -- Three sec ago: 2019-09-04 13:40:30.870

Get difference between dates

MS SQL server provides DATEDIFF(datepart ,startdate ,enddate) to get the difference between 2 dates. It will return the difference in term of datepart that you set. For example, if you set datepart as nanosecond, then it will return the total difference between your 2 dates in nanosecond.

-- Difference between dates.
SELECT DATEDIFF(day, '2009-05-02', '2009-05-25')                -- Difference of 23 days.
SELECT DATEDIFF(month, GETDATE(), DATEADD(month, 2, GETDATE())) -- Difference of 2 months.
MS SQL Server

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.