laika222
10/27/2016 - 5:49 PM

## Shows examples of how to calculate different dates.

Shows examples of how to calculate different dates.

``````-- Select current timestamp, including seconds (result: 2019-04-30 14:47:39.840)
SELECT GETDATE();

-- Alternate way to select current timestamp, including seconds (result: 2019-04-30 14:47:39.840)
SELECT CURRENT_TIMESTAMP;

-- Select current date (result: 2019-04-30)
SELECT CONVERT (DATE, GETDATE());

-- Select DAY from DATE or TIMESTAMP (result: 30)
SELECT DAY('2019-04-30 01:01:01.1234567');

-- Select MONTH from DATE or TIMESTAMP (result: 4)
SELECT MONTH(GETDATE());

-- Select YEAR from DATE or TIMESTAMP (result: 2019)
SELECT YEAR('2019-04-30 01:01:01.1234567');

-- Add an interval to a certain date - formula is time unit (year, month, day, etc.), amount to add or subtract (use negative number to go back in time), and the date you want to start from.

-- Calculate difference between two dates, returns result in the form of seconds - formula is SELECT DATEDIFF(time unit, start date, end date)
SELECT DATEDIFF(SECOND, '2005-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');

-- Calculate difference between two dates, returns result in the form of minutes - formula is SELECT DATEDIFF(time unit, start date, end date)
SELECT DATEDIFF(MINUTE, GETDATE(), '2019-01-01 00:00:00.0000000');

-- Calculate difference between two dates, returns result in the form of hours - formula is SELECT DATEDIFF(time unit, start date, end date)
SELECT DATEDIFF(HOUR, '2005-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');

-- Calculate difference between two dates, returns result in the form of days - formula is SELECT DATEDIFF(time unit, start date, end date)
SELECT DATEDIFF(DAY, GETDATE(), '2019-01-01 00:00:00.0000000');

-- Calculate difference between two dates, returns result in the form of years - formula is SELECT DATEDIFF(time unit, start date, end date)
SELECT DATEDIFF(YEAR, '2005-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');``````
``````-- Select current timestamp
SELECT NOW();

-- Select current date
SELECT CURDATE();

-- Alternate way to select current date
SELECT CURRENT_DATE;

-- Select current date and then add or subtract a certain interval of days
SELECT CURRENT_DATE + INTERVAL '7' DAY;

-- Select current timestamp and then add or subtract a certain interval of seconds
SELECT CURRENT_TIMESTAMP + INTERVAL '200' SECOND;

-- Calculate difference between two dates, and returns result in the form of seconds
SELECT TIMESTAMPDIFF(SECOND,'2016-01-01 00:00:00','2016-01-11 00:00:00');

-- Calculate difference between two dates, and returns result in the form of minutes
SELECT TIMESTAMPDIFF(MINUTE,NOW(),'2016-01-11 00:00:00');

-- Calculate difference between two dates, and returns result in the form of hours
SELECT TIMESTAMPDIFF(HOUR,'2016-01-01 00:00:00','2016-01-11 00:00:00');

-- Calculate difference between two dates, and returns result in the form of days
SELECT TIMESTAMPDIFF(DAY,CURRENT_DATE,'2016-01-11 00:00:00');

-- Calculate difference between two dates, and returns result in the form of years
SELECT TIMESTAMPDIFF(YEAR,'2016-01-01 00:00:00','2016-01-11 00:00:00');``````