How to enter and calculate dates in MS SQL

Summary

Most date calculations are actually built-in the SQL engine. SQL can directly add or subtract dates.

  1. Add 15 days
  2. Delete 5 months
  1. SELECT SALESDATE, DATEADD(DAY, 15, salesdate) AS D1, DATEADD(MONTH, -5, salesdate) AS D2, SALESQTY, SALESPRICE
  2. FROM sales;

Data used

  1. CREATE TABLE sales(
  2.         SALESDATE     DATETIME      NOT NULL,
  3.         SALESSTAMP    TIMESTAMP             ,
  4.         SALESQTY      DECIMAL(8,2)          ,
  5.         SALESPRICE    DECIMAL(8,2)
  6. );
  7. GO

The dates already include a time, if you do not enter the time with date, the time will be: 0:00:00.000 hh:mm:ss.ddd is the time to the thousand of a second.

  1. INSERT INTO SALES (SALESDATE, SALESQTY, SALESPRICE) VALUES ('October 10, 2005' ,10,11.75);
  2. INSERT INTO SALES (SALESDATE, SALESQTY, SALESPRICE) VALUES ('November 10, 2005 10:01:01',9,11.75);
  3. INSERT INTO SALES (SALESDATE, SALESQTY, SALESPRICE) VALUES ('December 6, 2005 11:01:01' ,8,11.75);
  4. INSERT INTO SALES (SALESDATE, SALESQTY, SALESPRICE) VALUES ('January 5, 2006',7,11.75);
  5. INSERT INTO SALES (SALESDATE, SALESQTY, SALESPRICE) VALUES ('02/07/2006' ,6,11.75);
  6. INSERT INTO SALES (SALESDATE, SALESQTY, SALESPRICE) VALUES ('03/08/2006 14:01:01',5,11.75);
  7. INSERT INTO SALES (SALESDATE, SALESQTY, SALESPRICE) VALUES ('04/06/2006 15:01:01',4,11.75);
  8. INSERT INTO SALES (SALESDATE, SALESQTY, SALESPRICE) VALUES ('20060504',3,11.75);
  9. INSERT INTO SALES (SALESDATE, SALESQTY, SALESPRICE) VALUES ('20060606 17:01:01',2,11.75);
  10. INSERT INTO SALES (SALESDATE, SALESQTY, SALESPRICE) VALUES ('20060706 19:01:01',1,11.75);
  11. GO
  12.  
  13. SELECT salesdate Sales, salesstamp AS Stamp, salesqty Qty, salesprice AS Price
  14. FROM sales;
  15. GO
Sales                     Stamp                 Qty     Price
-------------------------------------------------------------
2005-10-10 00:00:00.000   0x0000000000000FA2    10.00   11.75
2005-11-10 10:01:01.000   0x0000000000000FA3    9.00    11.75
2005-12-06 11:01:01.000   0x0000000000000FA4    8.00    11.75
2006-01-05 00:00:00.000   0x0000000000000FA5    7.00    11.75
2006-02-07 00:00:00.000   0x0000000000000FA6    6.00    11.75
2006-03-08 14:01:01.000   0x0000000000000FA7    5.00    11.75
2006-04-06 15:01:01.000   0x0000000000000FA8    4.00    11.75
2006-05-04 00:00:00.000   0x0000000000000FA9    3.00    11.75
2006-06-06 17:01:01.000   0x0000000000000FAA    2.00    11.75
2006-07-06 19:01:01.000   0x0000000000000FAB    1.00    11.75

Notice that I entered:

  • October 10, 2005: and got: 2005-10-10 00:00:00.000
  • November 10, 2005 10:01:01 and got: 2005-11-10 10:01:01.000
  • 02/07/2006 and got: 2006-02-07 00:00:00.000
  • 03/08/2006 14:01:01 and got: 2006-03-08 14:01:01.000
  • 20060504 and got: 2006-05-04 00:00:00.000
  • 20060606 17:01:01 and got: 2006-06-06 17:01:01.000
  • MS SQL Server is smart enough to analyze a date, and parse it properly without having to specify the format.

Timestamp in MS SQL Server is 'NOT' a date and time, it's just a unique Hex value and has no relation with neither the date nor the time.

Adding Days with MS SQL Server

  1. Add 15 days
  2. Delete 15 days
  1. SELECT SALESDATE, DATEADD(DAY, 15, salesdate) AS D1, DATEADD(DAY, -15, salesdate) AS D2, SALESQTY, SALESPRICE
  2. FROM sales;
  3. GO
SALESDATE                   D1                        D2                         Qty    Price
---------------------------------------------------------------------------------------------------
2005-10-10 00:00:00.000    2005-10-25 00:00:00.000    2005-09-25 00:00:00.000    10.00  11.75
2005-11-10 10:01:01.000    2005-11-25 10:01:01.000    2005-10-26 10:01:01.000    9.00   11.75
2005-12-06 11:01:01.000    2005-12-21 11:01:01.000    2005-11-21 11:01:01.000    8.00   11.75
2006-01-05 00:00:00.000    2006-01-20 00:00:00.000    2005-12-21 00:00:00.000    7.00   11.75
2006-02-07 00:00:00.000    2006-02-22 00:00:00.000    2006-01-23 00:00:00.000    6.00   11.75
2006-03-08 14:01:01.000    2006-03-23 14:01:01.000    2006-02-21 14:01:01.000    5.00   11.75
2006-04-06 15:01:01.000    2006-04-21 15:01:01.000    2006-03-22 15:01:01.000    4.00   11.75
2006-05-04 00:00:00.000    2006-05-19 00:00:00.000    2006-04-19 00:00:00.000    3.00   11.75
2006-06-06 17:01:01.000    2006-06-21 17:01:01.000    2006-05-22 17:01:01.000    2.00   11.75
2006-07-06 19:01:01.000    2006-07-21 19:01:01.000    2006-06-21 19:01:01.000    1.00   11.75
  • Just the dates have changed, the times have not.

Adding Months with MS SQL Server

  1. Add 3 months
  2. Delete 3 months
  1. SELECT SALESDATE, DATEADD(MONTH, 3, salesdate) AS D1, DATEADD(MONTH, -3, salesdate) AS D2, SALESQTY, SALESPRICE
  2. FROM sales;
  3. GO
SALESDATE                   D1                        D2                         Qty     Price
---------------------------------------------------------------------------------------------------
2005-10-10 00:00:00.000    2006-01-10 00:00:00.000    2005-07-10 00:00:00.000    10.00   11.75
2005-11-10 10:01:01.000    2006-02-10 10:01:01.000    2005-08-10 10:01:01.000    9.00    11.75
2005-12-06 11:01:01.000    2006-03-06 11:01:01.000    2005-09-06 11:01:01.000    8.00    11.75
2006-01-05 00:00:00.000    2006-04-05 00:00:00.000    2005-10-05 00:00:00.000    7.00    11.75
2006-02-07 00:00:00.000    2006-05-07 00:00:00.000    2005-11-07 00:00:00.000    6.00    11.75
2006-03-08 14:01:01.000    2006-06-08 14:01:01.000    2005-12-08 14:01:01.000    5.00    11.75
2006-04-06 15:01:01.000    2006-07-06 15:01:01.000    2006-01-06 15:01:01.000    4.00    11.75
2006-05-04 00:00:00.000    2006-08-04 00:00:00.000    2006-02-04 00:00:00.000    3.00    11.75
2006-06-06 17:01:01.000    2006-09-06 17:01:01.000    2006-03-06 17:01:01.000    2.00    11.75
2006-07-06 19:01:01.000    2006-10-06 19:01:01.000    2006-04-06 19:01:01.000    1.00    11.75
  • Just the dates have changed, the times have not.
  • When adding months, MS SQL will add the months, not the number of days. Months can be 28 days, 29 days, 30 days or 31 days. So when you add a months, it does not add 30 days but a whole month: 15-Feb-2006 + 1 month = 15-Mar-2006 or 28 days, 15-Feb-2006 + 2 month = 15-Apr-2006 or 59 days.

Adding Years with MS SQL Server

  1. Add 5 years
  2. Delete 5 years
  1. SELECT SALESDATE, DATEADD(YEAR, 5, SALESDATE) AS Y1, DATEADD(YEAR, -5, SALESDATE) AS Y2, SALESQTY QTY
  2. FROM sales;
SALESDATE                   D1                        D2                         Qty
-----------------------------------------------------------------------------------------
2005-10-10 00:00:00.000    2010-10-10 00:00:00.000    2000-10-10 00:00:00.000    10.00
2005-11-10 10:01:01.000    2010-11-10 10:01:01.000    2000-11-10 10:01:01.000    9.00
2005-12-06 11:01:01.000    2010-12-06 11:01:01.000    2000-12-06 11:01:01.000    8.00
2006-01-05 00:00:00.000    2011-01-05 00:00:00.000    2001-01-05 00:00:00.000    7.00
2006-02-07 00:00:00.000    2011-02-07 00:00:00.000    2001-02-07 00:00:00.000    6.00
2006-03-08 14:01:01.000    2011-03-08 14:01:01.000    2001-03-08 14:01:01.000    5.00
2006-04-06 15:01:01.000    2011-04-06 15:01:01.000    2001-04-06 15:01:01.000    4.00
2006-05-04 00:00:00.000    2011-05-04 00:00:00.000    2001-05-04 00:00:00.000    3.00
2006-06-06 17:01:01.000    2011-06-06 17:01:01.000    2001-06-06 17:01:01.000    2.00
2006-07-06 19:01:01.000    2011-07-06 19:01:01.000    2001-07-06 19:01:01.000    1.00

When adding years, MS SQL will add the years, not the number of days. Years can be 365 days or 366 days for leap years.

Date functions with MS SQL Server

See: How to get the date portions from datetime with SQL Server