|
세기 포함 안함(yy) |
세기 포함(yyyy) |
표준 |
입력/출력** |
- |
0 또는 100 (*) |
기본값 |
mon dd yyyy hh:miAM(또는 PM) |
1 |
101 |
USA |
mm/dd/yy |
2 |
102 |
ANSI |
yy.mm.dd |
3 |
103 |
영국/프랑스 |
dd/mm/yy |
4 |
104 |
독일 |
dd.mm.yy |
5 |
105 |
이탈리아 |
dd-mm-yy |
6 |
106 |
- |
dd mon yy |
7 |
107 |
- |
Mon dd, yy |
8 |
108 |
- |
hh:mm:ss |
- |
9 또는 109 (*) |
기본값 + 밀리초 |
mon dd yyyy hh:mi:ss:mmmAM(또는 PM) |
10 |
110 |
USA |
mm-dd-yy |
11 |
111 |
일본 |
yy/mm/dd |
12 |
112 |
ISO |
yymmdd |
- |
13 또는 113 (*) |
유럽 기본값 + 밀리초 |
dd mon yyyy hh:mm:ss:mmm(24h) |
14 |
114 |
- |
hh:mi:ss:mmm(24h) |
- |
20 또는 120 (*) |
ODBC 표준 |
yyyy-mm-dd hh:mi:ss(24h) |
- |
21 또는 121 (*) |
ODBC 표준(밀리초) |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
- |
126(***) |
ISO8601 |
yyyy-mm-dd Thh:mm:ss.mmm(스페이스 없음) |
- |
130* |
회교식**** |
dd mon yyyy hh:mi:ss:mmmAM |
- |
131* |
회교식**** |
dd/mm/yy hh:mi:ss:mmmAM |
--------------------------------------------------------------------------------
날짜 포맷 변환
--------------------------------------------------------------------------------
[ 형식 : 'YYYY.MM.DD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY.MM.DD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 102)
3. DB2 : REPLACE(CHAR(DATE(date_exp),ISO), '-', '.')
[ 형식 : 'HH:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'HH:MI:SS')
2. MSSQL : CONVERT(VARCHAR, date_exp, 108)
3. DB2 : CHAR(TIME(date_exp) , JIS )
[ 형식 : 'YYYY/MM/DD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY/MM/DD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 111)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '/')
[ 형식 : 'YYYYMMDD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYYMMDD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 112)
3. DB2 : CHAR(DATE(date_exp))
[ 형식 : 'HH24:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'HH24:MI:SS')
2. MSSQL : CONVERT(VARCHAR(8), date_exp, 114)
3. DB2 : CHAR(TIME(date_exp))
[ 형식 : 'YYYY.MM.DD HH24:MI' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY.MM.DD HH24:MI')
2. MSSQL : CONVERT(VARCHAR, date_exp, 102) + ' ' + CONVERT(VARCHAR(5), date_exp, 114)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '.') || CAST( TIME(date_exp) AS CHAR(5))
[ 형식 : 'YYYY/MM/DD HH24:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY/MM/DD HH24:MI:SS')
2. MSSQL : CONVERT(VARCHAR, date_exp, 111) + ' ' + CONVERT(VARCHAR(8), date_exp, 114)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') || CAST( TIME(date_exp))
-- 숫자만 추출 예제
CREATE FUNCTION ep_GetOnlyNumber(@Str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @retStr varchar(MAX)
SET @retStr = ''
DECLARE @i int
SET @i = 1
While @i <= Len(@Str)
BEGIN
--if isnumeric(substring(@str,@i,1)) = 1 -- isnumeric 사용하면 '-' 도 가져오기 때문에 안됨.
IF substring(@Str,@i,1) BETWEEN '0' AND '9'
SET @retStr = @retStr + substring(@Str,@i,1)
SET @i = @i + 1
END
RETURN @retStr
END
--예제 용 10분뒤의 날짜 20100118000000 14자리형으로 변환
dbo.ep_GetOnlyNumber(CONVERT(varchar, dateadd(mi, -10, getdate()),20))
-->
--********************************************************************
-- YEAR,MONTH,DAY,DATEDIFF함수(날짜열)
--********************************************************************
--year, month, day 해당일의 년월일 값을 돌려준다.
select year(getdate())'year',
month(getdate())'month',
day(getdate())'day'
--datediff(part,date1,date2)/두 날짜 사이의 DATE구분 사이 값을 계산함.
-- (part, date1, date2)
select datediff(dd,'1999/02/15','2000/02/15')'day',
datediff(mm,'1999/02/15','2000/02/15')'month',
datediff(yy,'1999/02/15','2000/02/15')'year'
--
select datediff(dd,'1999/11/06','2000/02/15')'day',
datediff(mm,'1999/11/06','2000/02/15')'month',
datediff(yy,'1999/11/06','2000/02/15')'year'
--회원테이블의 생일과 오늘의 일수 차이를 검색! (Alias는 회원명, 생일, 오늘, 태어난지?)
select mem_name"회원명", mem_bir"생일",
getdate()"오늘",
datediff(dd,mem_bir,getdate())"태어난지?"
from member
--본인의 생년월일과 오늘의 일수 차이를 비교 검색!
select datediff(dd,'1972/08/14',getdate()) "차이는"
--*******************************************************************
-- Datename, Datepart함수(날짜열)
--*******************************************************************
--datename(part,date)/date에서 datepart부분의 ASCII값(구분문자)
--datepart(part,date)/date에서 datepart부분의 정수값(구분숫자)
select convert(char,getdate()), '<==현재날짜'
select datename(yy,getdate()),datepart(yy,getdate()),'<==년도'
select datename(qq,getdate()),datepart(qq,getdate()),'<==분기'
select datename(mm,getdate()),datepart(mm,getdate()),'<==월'
select datename(dy,getdate()),datepart(dy,getdate()),'<==일수'
select datename(dd,getdate()),datepart(dd,getdate()),'<==해당일'
select datename(wk,getdate()),datepart(wk,getdate()),'<==주수'
select datename(dw,getdate()),datepart(dw,getdate()),'<==요일'
select datename(hh,getdate()),datepart(hh,getdate()),'<==시간'
--회원테이블에서 구길동회원의 생일의 DATENAME 과 DATEPART를 검색!
--(Alias 는 회원명, 생일, 기타 上 同)
select mem_name"회원명", mem_bir"생일",
datename(dw,mem_bir)'기타上同',
datepart(dw,mem_bir)'기타上同'
from member
where mem_name='구길동'
/* 월 (2002년 2월)만 입력받아 해당월의 시작일과 종료일을 검색!
Alias는 해당월, 시작일, 종료일) */
--******************** 1. 2002-02-01의 1달 후 -1....
select '200년02월' 해당월, '200-02-01' 시작일,
dateadd(mm,1,'2000-02-01')-1 "종료일"
--******************** 2. 2002-03-01의 1일 전....
select '200년02월' 해당월, '200-02-01' "시작일",
dateadd(dd,-1,'2000-03-01') "종료일"
SQL 기타 함수
select convert(numeric,'123456.9') result1,
cast('123456.9' as numeric) result2
--
select convert(numeric(10,2),'123456.9') result1,
cast('123456.9' as numeric(10,2)) result2
--
select convert(float,'123456.9') result1,
cast('123456.9' as float) result2
--
select convert(decimal,'123456.9') result1,
cast('123456.9' as decimal) result2
--***********************************************************************
-- 함수(conversion) : 날짜로 치환
--***********************************************************************
--날짜로 치환/모양만 날짜형인 문자를 DATE로 치환
select'19990101'result1,'1999-01-01'result2,
'1999-01-01 00:00:00.000'result3
--
select convert(datetime,'20201025',112)" result1",
convert(datetime,'20201025')" result2",
convert(datetime,'2020-10-25 10:15:20.000') " result3",
cast('2020-10-25 10:15:20.000' as datetime) " result4"
--
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) 치환날짜
from member
--1900년대 사람들만 있다고 가정했을 경우 19를 더해서 밀레니엄버그를 없앨 수 가 있다.
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 500일을 더한 날짜를 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) + 500 치환날짜
from member
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 '1974-01-01'부터 '1975-12-31'사이의 날짜를 검색!
--(Alias 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) 치환날짜
from member
where convert(datetime,'19'+mem_regno1)between'1974-01-01'and'1975-12-31'
--회원테이블에서 생일을 문자로 치환한 후 LIKE '1975%'의 조건을 사용하여 해당회원을 검색!
--Alias는 회원명, 생일)
select mem_name 회원명, mem_bir 생일
from member
where convert(char, mem_bir, 121) like '1975%'
--is null, is not null /null값인지 아닌지 비교
--isnull(c,d)/c가 null값이면 d값으로 치환!
--nullif(c,d)/c와 d를 비교하여 같으면 null을, 다르면 c값을 돌려준다.
--AVG(columm)/조회범위 내에 해당 컬럼들의 평균값
-- /DISTINCT : 중복된 값은 제외
-- /ALL : Default로써 모든 값을 포함(all을 쓰지 않아도 Default값으로 적용)
-- /Column명 : NULL값은 제외
-- /* : NULL값도 포함(COUNT함수만 사용)
-- isnull을 사용하여 NUll값은 '0'등으로 나오게 한다.
--ISDATE(c) / 타당한 날짜 포맷인지 확인 : 날짜면 1, 아니면 0
--ISNUMERIC(n) / 타당한 숫자포맷인지 확인 : 숫자면 1, 아니면 0
--CASE WHEN / 연속적인 조건문(자주활용되는 함수)
-- CASE WHEN ~ THEN ~ ELSE ~ END
세기 포함 안함(yy)
세기 포함(yyyy)
표준
입력/출력**
--------------------------------------------------------------------------------
날짜 포맷 변환
--------------------------------------------------------------------------------
[ 형식 : 'YYYY.MM.DD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY.MM.DD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 102)
3. DB2 : REPLACE(CHAR(DATE(date_exp),ISO), '-', '.')
[ 형식 : 'HH:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'HH:MI:SS')
2. MSSQL : CONVERT(VARCHAR, date_exp, 108)
3. DB2 : CHAR(TIME(date_exp) , JIS )
[ 형식 : 'YYYY/MM/DD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY/MM/DD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 111)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '/')
[ 형식 : 'YYYYMMDD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYYMMDD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 112)
3. DB2 : CHAR(DATE(date_exp))
[ 형식 : 'HH24:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'HH24:MI:SS')
2. MSSQL : CONVERT(VARCHAR(8), date_exp, 114)
3. DB2 : CHAR(TIME(date_exp))
[ 형식 : 'YYYY.MM.DD HH24:MI' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY.MM.DD HH24:MI')
2. MSSQL : CONVERT(VARCHAR, date_exp, 102) + ' ' + CONVERT(VARCHAR(5), date_exp, 114)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '.') || CAST( TIME(date_exp) AS CHAR(5))
[ 형식 : 'YYYY/MM/DD HH24:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY/MM/DD HH24:MI:SS')
2. MSSQL : CONVERT(VARCHAR, date_exp, 111) + ' ' + CONVERT(VARCHAR(8), date_exp, 114)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') || CAST( TIME(date_exp))
-- 숫자만 추출 예제
CREATE FUNCTION ep_GetOnlyNumber(@Str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @retStr varchar(MAX)
SET @retStr = ''
DECLARE @i int
SET @i = 1
While @i <= Len(@Str)
BEGIN
--if isnumeric(substring(@str,@i,1)) = 1 -- isnumeric 사용하면 '-' 도 가져오기 때문에 안됨.
IF substring(@Str,@i,1) BETWEEN '0' AND '9'
SET @retStr = @retStr + substring(@Str,@i,1)
SET @i = @i + 1
END
RETURN @retStr
END
--예제 용 10분뒤의 날짜 20100118000000 14자리형으로 변환
dbo.ep_GetOnlyNumber(CONVERT(varchar, dateadd(mi, -10, getdate()),20))
-->
--********************************************************************
-- YEAR,MONTH,DAY,DATEDIFF함수(날짜열)
--********************************************************************
--year, month, day 해당일의 년월일 값을 돌려준다.
select year(getdate())'year',
month(getdate())'month',
day(getdate())'day'
--datediff(part,date1,date2)/두 날짜 사이의 DATE구분 사이 값을 계산함.
-- (part, date1, date2)
select datediff(dd,'1999/02/15','2000/02/15')'day',
datediff(mm,'1999/02/15','2000/02/15')'month',
datediff(yy,'1999/02/15','2000/02/15')'year'
--
select datediff(dd,'1999/11/06','2000/02/15')'day',
datediff(mm,'1999/11/06','2000/02/15')'month',
datediff(yy,'1999/11/06','2000/02/15')'year'
--회원테이블의 생일과 오늘의 일수 차이를 검색! (Alias는 회원명, 생일, 오늘, 태어난지?)
select mem_name"회원명", mem_bir"생일",
getdate()"오늘",
datediff(dd,mem_bir,getdate())"태어난지?"
from member
--본인의 생년월일과 오늘의 일수 차이를 비교 검색!
select datediff(dd,'1972/08/14',getdate()) "차이는"
--*******************************************************************
-- Datename, Datepart함수(날짜열)
--*******************************************************************
--datename(part,date)/date에서 datepart부분의 ASCII값(구분문자)
--datepart(part,date)/date에서 datepart부분의 정수값(구분숫자)
select convert(char,getdate()), '<==현재날짜'
select datename(yy,getdate()),datepart(yy,getdate()),'<==년도'
select datename(qq,getdate()),datepart(qq,getdate()),'<==분기'
select datename(mm,getdate()),datepart(mm,getdate()),'<==월'
select datename(dy,getdate()),datepart(dy,getdate()),'<==일수'
select datename(dd,getdate()),datepart(dd,getdate()),'<==해당일'
select datename(wk,getdate()),datepart(wk,getdate()),'<==주수'
select datename(dw,getdate()),datepart(dw,getdate()),'<==요일'
select datename(hh,getdate()),datepart(hh,getdate()),'<==시간'
--회원테이블에서 구길동회원의 생일의 DATENAME 과 DATEPART를 검색!
--(Alias 는 회원명, 생일, 기타 上 同)
select mem_name"회원명", mem_bir"생일",
datename(dw,mem_bir)'기타上同',
datepart(dw,mem_bir)'기타上同'
from member
where mem_name='구길동'
/* 월 (2002년 2월)만 입력받아 해당월의 시작일과 종료일을 검색!
Alias는 해당월, 시작일, 종료일) */
--******************** 1. 2002-02-01의 1달 후 -1....
select '200년02월' 해당월, '200-02-01' 시작일,
dateadd(mm,1,'2000-02-01')-1 "종료일"
--******************** 2. 2002-03-01의 1일 전....
select '200년02월' 해당월, '200-02-01' "시작일",
dateadd(dd,-1,'2000-03-01') "종료일"
SQL 기타 함수
select convert(numeric,'123456.9') result1,
cast('123456.9' as numeric) result2
--
select convert(numeric(10,2),'123456.9') result1,
cast('123456.9' as numeric(10,2)) result2
--
select convert(float,'123456.9') result1,
cast('123456.9' as float) result2
--
select convert(decimal,'123456.9') result1,
cast('123456.9' as decimal) result2
--***********************************************************************
-- 함수(conversion) : 날짜로 치환
--***********************************************************************
--날짜로 치환/모양만 날짜형인 문자를 DATE로 치환
select'19990101'result1,'1999-01-01'result2,
'1999-01-01 00:00:00.000'result3
--
select convert(datetime,'20201025',112)" result1",
convert(datetime,'20201025')" result2",
convert(datetime,'2020-10-25 10:15:20.000') " result3",
cast('2020-10-25 10:15:20.000' as datetime) " result4"
--
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) 치환날짜
from member
--1900년대 사람들만 있다고 가정했을 경우 19를 더해서 밀레니엄버그를 없앨 수 가 있다.
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 500일을 더한 날짜를 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) + 500 치환날짜
from member
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 '1974-01-01'부터 '1975-12-31'사이의 날짜를 검색!
--(Alias 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) 치환날짜
from member
where convert(datetime,'19'+mem_regno1)between'1974-01-01'and'1975-12-31'
--회원테이블에서 생일을 문자로 치환한 후 LIKE '1975%'의 조건을 사용하여 해당회원을 검색!
--Alias는 회원명, 생일)
select mem_name 회원명, mem_bir 생일
from member
where convert(char, mem_bir, 121) like '1975%'
--is null, is not null /null값인지 아닌지 비교
--isnull(c,d)/c가 null값이면 d값으로 치환!
--nullif(c,d)/c와 d를 비교하여 같으면 null을, 다르면 c값을 돌려준다.
--AVG(columm)/조회범위 내에 해당 컬럼들의 평균값
-- /DISTINCT : 중복된 값은 제외
-- /ALL : Default로써 모든 값을 포함(all을 쓰지 않아도 Default값으로 적용)
-- /Column명 : NULL값은 제외
-- /* : NULL값도 포함(COUNT함수만 사용)
-- isnull을 사용하여 NUll값은 '0'등으로 나오게 한다.
--ISDATE(c) / 타당한 날짜 포맷인지 확인 : 날짜면 1, 아니면 0
--ISNUMERIC(n) / 타당한 숫자포맷인지 확인 : 숫자면 1, 아니면 0
--CASE WHEN / 연속적인 조건문(자주활용되는 함수)
-- CASE WHEN ~ THEN ~ ELSE ~ END