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
Data used
- SELECT sales_date, sales_qty
- FROM dbo.sales
- WHERE YEAR(sales_date) = 2005;
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;
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;
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;
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;
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)