3.1 Extract Year, Month and Day

Extraction of parts of a date or datetime can be done as follows:

SELECT YEAR(Date_Column) AS _year, 
       MONTH(Date_Column) AS _month, 
       DAY(Date_Column) AS _day
    FROM Dates_Table
    ;

SELECT YEAR(Datetime_Column) AS _year, 
       MONTH(Datetime_Column) AS _month, 
       DAY(Datetime_Column) AS _day
    FROM Dates_Table
    ;

3.2 Extract Year, Month, Day, Hour, Minute, Second and iso week

Of course, much more detailed information can be extracted from a date as long as the file format allows it.

For this purpose we use the DATEPART function.

SELECT DATEPART(YEAR, Date_Column) AS _year, 
       DATEPART(MONTH, Date_Column) AS _month, 
       DATEPART(DAY, Date_Column) AS _day
    FROM Dates_Table
    ;

Let’s try the hours now.

SELECT DATEPART(YEAR, Date_Column) AS _year, 
       DATEPART(MONTH, Date_Column) AS _month, 
       DATEPART(DAY, Date_Column) AS _day, 
       DATEPART(HOUR, Date_Column) AS _hour
    FROM Dates_Table
    ;

This, of course, no longer works. This is what I meant earlier by ‘as long as the file format allows’. The column ‘Date_Column’ contains only date values, no other as it is the case for example with the column ‘Datetime’.

Let’s try this column next.

SELECT DATEPART(YEAR, Datetime_Column) AS _year, 
       DATEPART(MONTH, Datetime_Column) AS _month, 
       DATEPART(DAY, Datetime_Column) AS _day, 
       DATEPART(HOUR, Datetime_Column) AS _hour, 
       DATEPART(MINUTE, Datetime_Column) AS _minute, 
       DATEPART(SECOND, Datetime_Column) AS _second
    FROM Dates_Table
    ;

Works perfectly. We can also output the associated calendar week:

SELECT DATEPART(ISO_WEEK, Datetime_Column) AS _iso_week
    FROM Dates_Table
    ;

For example, if we want to get the name of the day or month, we can do this with the DATENAME function (here I have all parts of the date output as a name).

SELECT DATENAME (YEAR, Datetime_Column) AS _year_name,
       DATENAME (MONTH, Datetime_Column) AS _month_name,
       DATENAME (DAY, Datetime_Column) AS _day_name,
       DATENAME (WEEKDAY, Datetime_Column) AS _weekday_name,
       DATENAME (HOUR, Datetime_Column) AS _hour_name,
       DATENAME (MINUTE, Datetime_Column) AS _minute_name,
       DATENAME (SECOND, Datetime_Column) AS _second_name
    FROM Dates_Table
    ;

3.3 ORDER and GROUP BY

Mostly one uses the ORDER or GROUP BY function with dates.

For these examples, I am still enriching our previous table with the following sample data:

INSERT INTO Dates_Table VALUES ('2021-02-12', '2021-02-12 15:22:15')
INSERT INTO Dates_Table VALUES ('2021-01-17', '2021-01-17 15:22:15')
INSERT INTO Dates_Table VALUES ('2021-04-22', '2021-04-22 15:22:15')
INSERT INTO Dates_Table VALUES ('2021-05-15', '2021-05-15 15:22:15')
INSERT INTO Dates_Table VALUES ('2021-03-01', '2021-03-01 15:22:15')
INSERT INTO Dates_Table VALUES ('2021-05-21', '2021-05-21 15:22:15')
INSERT INTO Dates_Table VALUES ('2021-05-20', '2021-05-20 15:22:15')

SELECT * FROM Dates_Table;

Now a few Order By and Group By examples:

SELECT ID, Datetime_Column
    FROM Dates_Table
    ORDER BY Date_Column
    ;

SELECT ID, 
       DATEPART (DAY, Datetime_Column) AS _day
    FROM Dates_Table
    ORDER BY _day
    ;

SELECT DATENAME (WEEKDAY, Datetime_Column), 
       COUNT(*) AS count_weekday  
    FROM Dates_Table
    GROUP BY DATENAME (WEEKDAY, Datetime_Column)
    ORDER BY count_weekday DESC
    ;

3.4 Time Span

What is used at least as often are the indications of time spans that lie between two dates.

SQL Server also offers a command for this: DATEDIFF

For this purpose we will create another example table:

CREATE TABLE Time_Span_Table
    (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
     Date_1 DATETIME NOT NULL,
     Date_2 DATETIME NOT NULL)
    ;

INSERT INTO Time_Span_Table VALUES ('2021-05-19 15:22:15', '2021-05-22 18:45:59')

SELECT * FROM Time_Span_Table;

The syntax for DATEDIFF is quite simple: DATEDIFF ( datepart , startdate , enddate )

Here is an example:

SELECT DATEDIFF (YEAR, Date_1, Date_2) AS _time_span_year,
       DATEDIFF (MONTH, Date_1, Date_2) AS _time_span_month,
       DATEDIFF (DAY, Date_1, Date_2) AS _time_span_day,
       DATEDIFF (HOUR, Date_1, Date_2) AS _time_span_hour,
       DATEDIFF (MINUTE, Date_1, Date_2) AS _time_span_minute,
       DATEDIFF (SECOND, Date_1, Date_2) AS _time_span_second
    FROM Time_Span_Table
    ;