Functions returning date and time parts

Functions for Manipulating Data in SQL Server

Ana Voicu

Data Engineer

YEAR(date)

Returns the year from the specified date

SELECT 
    first_name,
    first_vote_date,
    YEAR(first_vote_date) AS first_vote_year
FROM voters;

| first_name | first_vote_date | first_vote_year |
|------------|-----------------|-----------------|
| Carol      | 2015-03-09      | 2015            |
| Ana        | 2015-01-17      | 2015            |
| Melissa    | 2015-04-09      | 2015            |

MONTH(date)

Returns the month from the specified date

SELECT 
    first_name,
    first_vote_date,
    YEAR(first_vote_date) AS first_vote_year,
    MONTH(first_vote_date) AS first_vote_month
FROM voters;

| first_name | first_vote_date | first_vote_year | first_vote_month |
|------------|-----------------|-----------------|------------------|
| Carol      | 2015-03-09      | 2015            | 3                |
| Ana        | 2015-01-17      | 2015            | 1                |
| Melissa    | 2015-04-09      | 2015            | 4                |

DAY(date)

Returns the day from the specified date

SELECT 
    first_name,
    first_vote_date,
    YEAR(first_vote_date) AS first_vote_year,
    MONTH(first_vote_date) AS first_vote_month,
    DAY(first_vote_date) AS first_vote_day
FROM voters;

| first_name | first_vote_date | first_vote_year | first_vote_month | first_vote_day |
|------------|-----------------|-----------------|------------------|----------------|
| Carol      | 2015-03-09      | 2015            | 3                | 9              |
| Ana        | 2015-01-17      | 2015            | 1                | 17             |
| Melissa    | 2015-04-09      | 2015            | 4                | 9              |

DATENAME(datepart, date)

Returns a character string representing the specified date part of the given date

datepart	abbreviations
year	yy, yyyy
month	mm, m
dayofyear	dy, y
week	wk, ww
weekday	dw, w

DATENAME() example

DECLARE @date datetime = '2019-03-24'
SELECT 
    YEAR(@date) AS year,
    DATENAME(YEAR, @date) AS year_name,
    MONTH(@date) AS month,
    DATENAME(MONTH, @date) AS month_name,
    DAY(@date) AS day,
    DATENAME(DAY, @date) AS day_name,
    DATENAME(WEEKDAY, @date) AS weekday

| year | year_name | month | month_name | day | day_name | weekday |
|------|-----------|-------|------------|-----|----------|---------|
| 2019 | 2019      | 3     | March      | 24  | 24       | Sunday  |

DATEPART(datepart, date)

It is similar to DATENAME()
Returns an integer representing the specified part of the given date

 DECLARE @date datetime = '2019-03-24'
SELECT 
    DATEPART(YEAR, @date) AS year_name,
    DATENAME(YEAR, @date) AS year_name,
    DATEPART(MONTH, @date) AS month_name,
    DATENAME(MONTH, @date) AS month_name

| year | year_name | month | month_name | 
|------|-----------|-------|------------|
| 2019 | 2019      | 3     | March      |

DATEFROMPARTS(year, month, day)

Receives 3 parameters: year, month, and day values
Generates a date

SELECT 
    DATEFROMPARTS(2019, 3, 5) AS new_date;

| new_date   | 
|------------|
| 2019-03-05 |

DATEFROMPARTS(year, month, day)

SELECT 
    YEAR('2019-03-05')  AS date_year,
    MONTH('2019-03-05') AS date_month,
    DAY('2019-03-05')   AS date_day,
    DATEFROMPARTS(YEAR('2019-03-05'), MONTH('2019-03-05'), DAY('2019-03-05')) AS reconstructed_date;

| date_year | date_month | date_day | reconstructed_date |
|-----------|------------|----------|--------------------|
| 2019      | 3          | 5        | 2019-03-05         |

Let's practice!

Functions for Manipulating Data in SQL Server