[edit]
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
[edit]
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襯
- precision = 7 企, fraction 100 nanoseconds襯
- 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
*/
[edit]
3 String functions #
- concat
- format
select concat('hello ', 111, ' world', '!') --, 覓語 ..
/*
------------
hello 111 world!
*/
[edit]
4 Conversion functions #
![[http]](/moniwiki/imgs/http.png)
parse
- 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
[edit]
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
*/
[edit]
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
[edit]
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覦襴豐
鏤