1 예제 테이블

2 Date and time functions

3 String functions

4 Conversion functions

5 Logical functions

6 EOMONTH()

7 format()

8 compress/decompress

9 참고자료

--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

• 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
*/

• concat
  
• format
  
  • books online(http://technet.microsoft.com/en-us/library/hh213505(SQL.110).aspx)
    
  • Formatting Types(http://technet.microsoft.com/en-us/library/hh213505(SQL.110).aspx)
    
  • CultureInfo Class(http://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo%28VS.71%29.aspx)
    
  • sql2011_ctp3_format_function.docx --> 좋은 예제1(http://sqlblog.com/blogs/aaron_bertrand/archive/2011/08/09/sql-server-v-next-denali-ctp3-t-sql-enhancements-format.aspx), 좋은 예제2(http://redju.tistory.com/148)
    
    

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

• 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

• 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
*/

함수의 매개변수로 받은 날짜의 마지막날을 리턴하는 함수. 조금 덜 귀찮아질 듯...

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

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밀리초

• Built-in functions for compression/decompression in SQL Server 2016(http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/12/08/built-in-functions-for-compression-decompression-in-sql-server-2016.aspx)
  

• New Built-In TSQL Functions in SQL Server 2012(http://www.sql-server-performance.com/2012/tsql-functions-sql-server-2012/)