[SQL] DATE_FORMAT, Null 처리

DATE_FORMAT

• date 타입을 원하는 형식의 문자열로 변환할 수 있는 함수

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS DATE FROM DUAL;

 

Date and Time Format Specifiers 문서

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

Specifier Description

%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u	Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V	Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v	Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal % character
%x	x, for any “x” not listed above

Oracle ver.

2022.11.09 - [프로그래밍 언어/SQL-Oracle] - [SQL] SQL 함수 (문자, 숫자, 날짜, 형변환, 집합, 분석)

[SQL] SQL 함수 (문자, 숫자, 날짜, 형변환, 집합, 분석)

---

Null 처리

IFNULL

• select 문에서 null 값 대체, 치환 가능한 함수
• Oracle의 NVL

select ifnull(필드명, '치환값') from 테이블명;
select ifnull(expr1, expr2) from 테이블명;

• expr1이 NULL인 경우 expr2 값을 반환
• expr1이 NULL이 아닌 경우에 expr1 값을 반환

TLNO = null 인 경우 NONE 삽입

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, 'NONE') AS TLNO
from PATIENT
where AGE <= 12 and GEND_CD = "W"
order by AGE desc, PT_NAME;

 

대체1. IF 함수 + IS NULL 조건

SELECT IF(IS NULL(TLNO), "NONE", TLNO) as TLNO
FROM PATIENT;

• TLNO가 NULL 이면 NONE 삽입

대체2. ISNULL()

SELECT ISNULL(TLNO, "NONE") as TLNO
FROM PATIENT;

CASE

COALESCE