How to get the date portions from datetime with MS SQL Server

How to get the date portions from datetime with SQL Server

Microsoft SQL Server, like almost all good SQL servers, has very rich date functions.
The date functions are 'NOT' SQL2003 compliant.
The date functions follow MS Visual Basic. This means that all of the date functionality will have to be rewritten when Microsoft moves to SQL2003 compatibility.

Date calculations

See: How to enter and calculate dates with SQL Server

Data used

SELECT sales_date, sales_qty 
FROM dbo.sales 
WHERE YEAR(sales_date) = 2005;

[Get Code]

sales_date                sales_qty
-----------------------   -----------
2005-10-06 10:01:01.000   10.00
2005-11-06 10:01:01.000   9.00
2005-12-06 11:01:01.000   8.00
2005-11-06 10:01:01.000   9.00
2005-12-06 11:01:01.000   8.00
2005-03-06 14:01:01.000   5.00
2005-10-06 10:01:01.000   10.00
2005-11-06 10:01:01.000   9.00
2005-12-06 11:01:01.000   8.00
2005-04-06 15:01:01.000   4.00
2005-05-06 16:01:01.000   3.00
2005-10-06 10:01:01.000   10.00
2005-11-06 10:01:01.000   9.00
2005-12-06 11:01:01.000   8.00
2005-10-06 10:01:01.000   10.00

(15 row(s) affected)

Extraction of date portions

Year

The are 2 ways of getting the years:

year(sales_date)
datepart(year,sales_date)

SELECT sales_date, YEAR(sales_date) AS 'Year1', DATEPART(YEAR,sales_date)AS 'Year2', sales_qty 
FROM dbo.sales 
WHERE YEAR(sales_date) = 2005;

[Get Code]

sales_date              Year1       Year2       sales_qty
----------------------- ----------- ----------- ---------------------------------------
2005-10-06 10:01:01.000 2005        2005        10.00
2005-11-06 10:01:01.000 2005        2005        9.00
2005-12-06 11:01:01.000 2005        2005        8.00
2005-11-06 10:01:01.000 2005        2005        9.00
2005-12-06 11:01:01.000 2005        2005        8.00
2005-03-06 14:01:01.000 2005        2005        5.00
2005-10-06 10:01:01.000 2005        2005        10.00
2005-11-06 10:01:01.000 2005        2005        9.00
2005-12-06 11:01:01.000 2005        2005        8.00
2005-04-06 15:01:01.000 2005        2005        4.00
2005-05-06 16:01:01.000 2005        2005        3.00
2005-10-06 10:01:01.000 2005        2005        10.00
2005-11-06 10:01:01.000 2005        2005        9.00
2005-12-06 11:01:01.000 2005        2005        8.00
2005-10-06 10:01:01.000 2005        2005        10.00

(15 row(s) affected)

Month

The are 4 ways of getting the month info:

month(sales_date)
datepart(month,sales_date)
datename(month,sales_date)
datepart(quarter,sales_date)

SELECT sales_date,  MONTH(sales_date) AS 'Month1',DATEPART(MONTH,sales_date) AS 'Month2', 
       DATENAME(MONTH,sales_date) AS 'Month Name', DATEPART(quarter,sales_date) AS 'Quarter',
       sales_qty 
FROM dbo.sales 
WHERE YEAR(sales_date) = 2005;

[Get Code]

sales_date              Month1      Month2      Month Name                     Quarter     sales_qty
----------------------- ----------- ----------- ------------------------------ ----------- ----------
2005-10-06 10:01:01.000 10          10          October                        4           10.00
2005-11-06 10:01:01.000 11          11          November                       4           9.00
2005-12-06 11:01:01.000 12          12          December                       4           8.00
2005-11-06 10:01:01.000 11          11          November                       4           9.00
2005-12-06 11:01:01.000 12          12          December                       4           8.00
2005-03-06 14:01:01.000 3           3           March                          1           5.00
2005-10-06 10:01:01.000 10          10          October                        4           10.00
2005-11-06 10:01:01.000 11          11          November                       4           9.00
2005-12-06 11:01:01.000 12          12          December                       4           8.00
2005-04-06 15:01:01.000 4           4           April                          2           4.00
2005-05-06 16:01:01.000 5           5           May                            2           3.00
2005-10-06 10:01:01.000 10          10          October                        4           10.00
2005-11-06 10:01:01.000 11          11          November                       4           9.00
2005-12-06 11:01:01.000 12          12          December                       4           8.00
2005-10-06 10:01:01.000 10          10          October                        4           10.00

(15 row(s) affected)

Week

The are 3 ways of getting the week info:

datepart(dayofyear,sales_date)
datepart(week,sales_date)
datename(weekday,sales_date)

SELECT sales_date,  DATEPART(dayofyear,sales_date) AS 'Day of Year', DATEPART(week,sales_date) AS 'Week', 
       DATENAME(weekday,sales_date) AS 'Weekday', sales_qty 
FROM dbo.sales 
WHERE YEAR(sales_date) = 2005;

[Get Code]

sales_date              Day of Year Week        Weekday                        sales_qty
----------------------- ----------- ----------- ------------------------------ ---------------------------------------
2005-10-06 10:01:01.000 279         41          Thursday                       10.00
2005-11-06 10:01:01.000 310         46          Sunday                         9.00
2005-12-06 11:01:01.000 340         50          Tuesday                        8.00
2005-11-06 10:01:01.000 310         46          Sunday                         9.00
2005-12-06 11:01:01.000 340         50          Tuesday                        8.00
2005-03-06 14:01:01.000 65          11          Sunday                         5.00
2005-10-06 10:01:01.000 279         41          Thursday                       10.00
2005-11-06 10:01:01.000 310         46          Sunday                         9.00
2005-12-06 11:01:01.000 340         50          Tuesday                        8.00
2005-04-06 15:01:01.000 96          15          Wednesday                      4.00
2005-05-06 16:01:01.000 126         19          Friday                         3.00
2005-10-06 10:01:01.000 279         41          Thursday                       10.00
2005-11-06 10:01:01.000 310         46          Sunday                         9.00
2005-12-06 11:01:01.000 340         50          Tuesday                        8.00
2005-10-06 10:01:01.000 279         41          Thursday                       10.00

(15 row(s) affected)

Time

The are 3 ways of getting the time info:

datepart(hour,sales_date)
datepart(minute,sales_date)
datepart(second,sales_date)

SELECT sales_date,  DATEPART(HOUR,sales_date) AS 'Hour', DATEPART(MINUTE,sales_date) AS 'Minutes',
       DATEPART(SECOND,sales_date) AS 'Seconds', sales_qty 
FROM dbo.sales 
WHERE YEAR(sales_date) = 2005;

[Get Code]

sales_date              Hour        Minutes     Seconds     sales_qty
----------------------- ----------- ----------- ----------- ---------------------------------------
2005-10-06 10:01:01.000 10          1           1           10.00
2005-11-06 10:01:01.000 10          1           1           9.00
2005-12-06 11:01:01.000 11          1           1           8.00
2005-11-06 10:01:01.000 10          1           1           9.00
2005-12-06 11:01:01.000 11          1           1           8.00
2005-03-06 14:01:01.000 14          1           1           5.00
2005-10-06 10:01:01.000 10          1           1           10.00
2005-11-06 10:01:01.000 10          1           1           9.00
2005-12-06 11:01:01.000 11          1           1           8.00
2005-04-06 15:01:01.000 15          1           1           4.00
2005-05-06 16:01:01.000 16          1           1           3.00
2005-10-06 10:01:01.000 10          1           1           10.00
2005-11-06 10:01:01.000 10          1           1           9.00
2005-12-06 11:01:01.000 11          1           1           8.00
2005-10-06 10:01:01.000 10          1           1           10.00

(15 row(s) affected)

All contents in SQLHacks.com are the property of db Networks Ltd and it's affiliates. All other trademarks not owned by db Networks Ltd or its affiliates that appear on the SQLHacks.com site are the property of their respective owners, especially: Windows^®, MS SQL^®, SQL Server^® which are the property of Microsoft Corp. DB2^® which is the property of IBM Systems. Oracle database^® which is the property of Oracle Corp.