How to extract portions of dates with MSSQL

Microsoft SQL Server has very rich date functions, it's a matter of using DATEPART, DATENAME, DATEADD, DATEDIFF and combining them to get almost every result wanted.

Datepart and datename

  1. SELECT CURRENT_TIMESTAMP AS 'Now',
  2.        DATEPART(YEAR, CURRENT_TIMESTAMP) AS 'Year',
  3.        DATEPART(MONTH, CURRENT_TIMESTAMP) AS 'Month 1',
  4.        DATENAME(MONTH,CURRENT_TIMESTAMP) AS 'Month 2',
  5.        DATEPART(DAY, CURRENT_TIMESTAMP) AS 'Day',
  6.        DATEPART(weekday, CURRENT_TIMESTAMP) AS 'Day 1',
  7.        DATENAME(weekday, CURRENT_TIMESTAMP) AS 'Day 2',
  8.        DATENAME(wk, CURRENT_TIMESTAMP) AS 'Day 3',
  9.        DATEPART(quarter, CURRENT_TIMESTAMP) AS 'Quarter',
  10.        DATEPART(week, CURRENT_TIMESTAMP) AS 'Week',
  11.        DATEPART(dayofyear, CURRENT_TIMESTAMP) AS 'Day of year',
  12.        RIGHT('00' + CAST(MONTH(CURRENT_TIMESTAMP) AS VARCHAR),2) AS 'month with leading 0',
  13.        RIGHT(CAST(YEAR(CURRENT_TIMESTAMP) AS VARCHAR),2) AS '2 digit year',
  14.        DATENAME(dw,CURRENT_TIMESTAMP)+', '+ CONVERT(CHAR(11),CURRENT_TIMESTAMP,106) AS 'Full date';
  15. go
Now                     Year        Month 1     Month 2                        Day         Day 1       Day 2                          Day 3                          Quarter     Week        Day of year month with leading 0 2 digit year Full date
----------------------- ----------- ----------- ------------------------------ ----------- ----------- ------------------------------ ------------------------------ ----------- ----------- ----------- -------------------- ------------ -------------------------------------------
2007-07-30 18:20:52.460 2007        7           July                           30          2           Monday                         31                             3           31          211         07                   07           Monday, 30 Jul 2007

(1 row(s) affected)

First and last day of the month

  1. The first day of the month is easy, it's always 01 with the month and the year.
  2. The last day of the month is more complicated, since it can either be the 28th, 29th, 30th or 31st. But since we know the the first of the next month, we just go back one day.
  1. SELECT CURRENT_TIMESTAMP AS 'Now',
  2.        CAST('01 '+ RIGHT(CONVERT(CHAR(11),CURRENT_TIMESTAMP,113),8) AS DATETIME) AS '1st day of this month',
  3.        DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST('01 '+ RIGHT(CONVERT(CHAR(11),CURRENT_TIMESTAMP,113),8) AS DATETIME))) AS 'Last day of this month';
  4. go
Now                     1st day of this month   Last day of this month
----------------------- ----------------------- -----------------------
2007-07-30 18:41:52.943 2007-07-01 00:00:00.000 2007-07-31 00:00:00.000

(1 row(s) affected)