--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
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 */
select concat('hello ', 111, ' world', '!') --숫자, 문자 섞일 수 있음.. /* ------------ hello 111 world! */
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
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
select format(getdate(), 'yyyy-MM-dd HH') --대소문자에 주의해야 한다.
select format(2, '00#') result /* result ---------------------- 002 */ select format(5000000000000/100000000, '#,#억') result /* result ---------------------- 50,000억 */
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밀리초