Contents

[-]
1 예제 테이블
2 Date and time functions
3 String functions
4 Conversion functions
5 Logical functions
6 EOMONTH()
7 format()
8 compress/decompress
9 참고자료


1 예제 테이블 #

--drop table #temp
create table #temp
(
    seq int identity(1,1)
,   std_dt date
,   val float
)
go

insert #temp values 
    ('20110801', 600)
,   ('20110801', 200)
,   ('20110801', 800)
,   ('20110802', 700)
,   ('20110802', 500)
,   ('20110802', 100)
,   ('20110803', 700)
,   ('20110803', 300)
,   ('20110803', 900)
go

2 Date and time functions #

  • datefromparts ( year, month, day )
  • datetime2fromparts ( year, month, day, hour, minute, seconds, fractions, precision )
    • precision = 7 이면, fraction은 100 nanoseconds를 표현할 수 있음
    • precision = 4 이면, fraction은 1 milliseconds를 표현할 수 있음
  • datetimefromparts ( year, month, day, hour, minute, seconds, milliseconds )
  • datetimeoffsetfromparts ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
  • eomonth ( start_date [, month_to_add ] )
  • smalldatetimefromparts ( year, month, day, hour, minute )
  • timefromparts ( hour, minute, seconds, fractions, precision )

select 
    DateFromParts ( 2010, 12, 31 )
,   Datetime2FromParts ( 2010, 12, 31, 23, 59, 59, 9999999, 7 )
,   DatetimeFromParts ( 2010, 12, 31, 23, 59, 59, 0 )

/*
---------- --------------------------- -----------------------
2010-12-31 2010-12-31 23:59:59.9999999 2010-12-31 23:59:59.000
*/

select
    EoMonth('20110109') --1월의 마지막날
,   EoMonth('20110109', 3) --4월(=1월+3개월)의 마지막날

/*
---------------------- ----------------------
2011-01-31 00:00:00.00 2011-04-30 00:00:00.00
*/

select 
    SMALLDATETIMEFROMPARTS ( 2010, 12, 31, 23, 59 )
,   TIMEFROMPARTS ( 23, 59, 59, 0, 0 )
/*
----------------------- ----------------
2010-12-31 23:59:00     23:59:59
*/

3 String functions #

select concat('hello ', 111, ' world', '!') --숫자, 문자 섞일 수 있음..
/*
------------
hello 111 world!
*/

4 Conversion functions #

  • [http]parse(http://technet.microsoft.com/en-us/library/hh213316(SQL.110).aspx)
  • try_convert
  • try_pase

select
    parse('12/16/2010' as datetime)
,   parse('000111' as int) --111
,   parse('20110229' as date) --error!! 
,   convert(date, '20110229') --error!! 

select
    parse('12/16/2010' as datetime)
,   parse('000111' as int) --111
,   try_parse('20110229' as date) --null
,   try_convert(date, '20110229') --null


5 Logical functions #

  • iif
  • choose

select
    iif(val >= 500, '500이상', '500미만') 조건
,   count(*) 건수
from #temp
group by
    iif(val >= 500, '500이상', '500미만')

/*
조건    건수
------- -----------
500미만   3
500이상   6
*/

select
    choose(3
    ,   min(case when std_dt = '20110801' then std_dt end)
    ,   min(case when std_dt = '20110802' then std_dt end)
    ,   min(case when std_dt = '20110803' then std_dt end)
    ,   min(case when std_dt = '20110804' then std_dt end)
    ,   min(case when std_dt = '20110805' then std_dt end)
    ) 선택
from #temp 

/*
선택
----------
2011-08-03
*/

6 EOMONTH() #

함수의 매개변수로 받은 날짜의 마지막날을 리턴하는 함수. 조금 덜 귀찮아질 듯...
DECLARE     
	@date DATE = '20110501';

SELECT     
	CurrentMonth   = EOMONTH(@date)		--2011-05-31
,    	FollowingMonth = EOMONTH(@date, 1)	--2011-06-30
,    	PreviousMonth  = EOMONTH(@date, -1);	--2011-04-30

7 format() #

format()함수는 정말 느리다. 귀찮을 때만 쓰자.

select format(getdate(), 'yyyy-MM-dd HH') --대소문자에 주의해야 한다.

select format(2, '00#') result

/*
result
----------------------
002
*/

select format(5000000000000/100000000, '#,#억') result

/*
result
----------------------
50,000억
*/

헐..미친..format()을 썼더니만.. 퍼포먼스가 아주 거지 같다. 대충 다음과 같이 테스트해보니 format()함수를 쓰지 않았을 때가 약 10배 빨랐다.
if object_id('tempdb.dbo.#time') is not null
    drop table #time
;with dummy
as
(
        select convert(datetime, '20000412') dt 
        union all
        select dateadd(hh, 1, dt) from dummy
        where dateadd(hh, 1, dt) < '20130513'
)
select 
	dt
into #time
from dummy
option (maxrecursion 0);
go

set statistics io on
set statistics time on

if object_id('tempdb.dbo.#1') is not null
    drop table #1

select
    dt
,   count(*) cnt
into #1
from (
    select 
        convert(smalldatetime, format(dt, 'yyyy-MM-dd HH:00')) dt
    from #time
) t
group by
    dt
option(maxdop 1, hash group)
--CPU 시간 = 8393밀리초, 경과 시간 = 9521밀리초

if object_id('tempdb.dbo.#2') is not null
    drop table #2

select 
    convert(smalldatetime, convert(char(13), dt, 121) + ':00') dt
,   count(*) cnt
into #2
from #time
group by
    convert(smalldatetime, convert(char(13), dt, 121) + ':00')
option(maxdop 1, hash group)
--CPU 시간 = 515밀리초, 경과 시간 = 974밀리초

8 compress/decompress #

9 참고자료 #

Retrieved from http://w.databaser.net/moniwiki/wiki.php/Built-inFunctions
last modified 2018-04-13 23:12:53