[SQL] DATEPART 실무 활용 문법

 

DATEPART 문법

DATEPART(WEEKDAY, DATE) AS DAY

 

1: Sun, 2:Mon, 3.Tue~

 

SELECT 
    DATE,
    DATEPART(YEAR, DATE) AS 년도,
    DATEPART(MONTH, DATE) AS 월,
    DATEPART(DAY, DATE) AS 일,
    DATEPART(WEEKDAY, DATE) AS 요일숫자,
    DATEPART(WEEK, DATE) AS 주차,
    DATEPART(QUARTER, DATE) AS 분기,
    DATEPART(HOUR, DATE) AS 시간,
    DATEPART(MINUTE, DATE) AS 분
FROM 테이블 T;

 

---

 

1. 요일별 매출 분석

SELECT 
    DATEPART(WEEKDAY, DATE) AS DAY,
    COUNT(*) AS Plays,
    SUM(BET) AS Total bet
FROM Game
GROUP BY DATEPART(WEEKDAY, DATE)
ORDER BY DAY;

 

2. 주말/ 평일 구분

SELECT 
    CASE 
        WHEN DATEPART(WEEKDAY, DATE) IN (1, 7) THEN '주말'  -- 일요일, 토요일
        ELSE '평일'
    END AS 주말평일구분,
    COUNT(*) AS 거래건수
FROM 거래테이블 T
GROUP BY 
    CASE 
        WHEN DATEPART(WEEKDAY, DATE) IN (1, 7) THEN '주말'
        ELSE '평일'
    END;

 

3. 요일별 프로모션

CASE DATEPART(WEEKDAY, DATE)
    WHEN 6 THEN 'Friday Bonus'
    WHEN 7 THEN 'Sat Free Meal'
    ELSE 'None'
END