sqlserver 获取当天时间到小时
SELECT CONVERT
(
VARCHAR ( 13 ),
DATEADD( HH, number, CONVERT ( VARCHAR ( 10 ), GETDATE( ), 120 ) ),
20
) AS d
FROM
MASTER..spt_values
WHERE
type = 'p'
AND DATEDIFF(
HH,
DATEADD( HH, number, CONVERT ( VARCHAR ( 10 ), GETDATE( ), 120 ) ),
CONVERT ( VARCHAR ( 10 ), DATEADD( DAY, 1, GETDATE( ) ), 120 )
) > 0
SQLServer 获取本年所有月份
with t as
(
select convert(varchar(7),dateadd(mm,t.number,dateadd(year, datediff(year, 0, getdate()), 0)),120) month
from
(select number from master..spt_values where type='P'AND number>=0 and number<=12) t
where year(dateadd(mm,t.number,dateadd(year, datediff(year, 0, getdate()), 0)))=year(getdate())
)
select month=ROW_NUMBER()OVER(ORDER BY t1.month),
t1.month year_month
from t t1
SQLServer、Oracle获取当前年份的1月到当前月之间的所有月份
sqlserver:
select convert(varchar(7),dateadd(mm,-t.number,getdate()),120)
from
(select number from master..spt_values where type='P') t
where year(dateadd(mm,-t.number,getdate()))=year(getdate())
order by convert(varchar(7),dateadd(mm,-t.number,getdate()),120)
oracle:
select to_char(add_months(sysdate, -t.rn), 'yyyy-mm')
from dual a, (select rownum - 1 rn from dual connect by rownum = 12) t
where to_char(add_months(sysdate, -t.rn), 'yyyy') =
to_char(sysdate, 'yyyy')
order by to_char(add_months(sysdate, -t.rn), 'yyyy-mm')