Funkce vracející části data a času

Funkce pro práci s daty v SQL Serveru

Ana Voicu

Data Engineer

YEAR(date)

Vrací rok ze zadaného data

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)

Vrací měsíc ze zadaného data

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)

Vrací den ze zadaného data

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)

Vrací řetězec znaků představující zadanou část daného data

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

Příklad funkce DATENAME()

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)

Podobná funkci DATENAME()
Vrací celé číslo představující zadanou část daného data

 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)

Přijímá 3 parametry: hodnoty roku, měsíce a dne
Generuje hodnotu typu 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         |

Pojďme procvičovat!

Funkce pro práci s daty v SQL Serveru