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 #

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

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 谿瑚襭 #