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.
SELECT DATEADD(day,-25,'2009-08-31')
-- 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');