날짜/시간 부분을 반환하는 함수

SQL Server에서 데이터 조작을 위한 함수

Ana Voicu

Data Engineer

YEAR(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)

지정한 날짜에서 월을 반환

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)

지정한 날짜에서 일을 반환

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)

지정한 날짜의 특정 부분을 문자형으로 반환

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

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)

DATENAME()과 유사함
지정한 날짜의 해당 부분을 나타내는 정수를 반환

 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)

매개변수 3개: 연, 월, 일 값
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         |

Ayo berlatih!

SQL Server에서 데이터 조작을 위한 함수