T-SQL取時間的年月日

經常保存在SQL Server的datetime格式的數據都是帶有時、分、秒等的。但是在頁面讀取的時候，通知只需要年月日，
今天用到的讀取時間年月日的方法如下：
view plaincopy to clipboardprint?
convert(varchar(12),時間列名,111)

如在數據表table1中的列operdate是datetime格式的，需要讀取operdate的年月日的方法就是
view plaincopy to clipboardprint?
select convert(varchar(12),operdate,111) from table1

只抓年、月、日: YEAR(), MONTH(), DAY(); 裡面參數放要抓的時間

-=================================================
--SQL中取得時間的一些技巧
--*********時間精確3毫秒*****************
--================================================ =
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

select DATEDIFF(mm,0,getdate())

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)


--------------當天半夜---------------------------------- ----------
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

--------------最後一天---------------------------------- ----------
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0)


----------去掉時分秒----------------------------
declare @a datetime
set @a = getdate()
SELECT @a,DATEADD(day,DATEDIFF(day,0,@a),0)

----------顯示星期幾----------------------------
select datename(weekday,getdate())

----------如何取得某個月天數-------------------
declare @m int
set @m=3 --月份
select datediff(day,'2006-'+cast(@m as varchar)+'-15' ,'2006-'+cast(@m+1 as varchar)+'-15')

----------或者使用計算本月的最後一天的腳本，然後用DAY函數區最後一天
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))

----------判斷是否閏年：------------------------
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平

年' else '閏年' end

select case datediff(day,datename(year,getdate())+'-02-01',dateadd(mm,1,datename(year,getdate())+'-02-

01'))
when 28 then '平年' else '閏年' end

----------一個季度多少天------------------------
declare @m tinyint,@time smalldatetime
select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
when @m between 4 and 6 then 4
when @m between 7 and 9 then 7
else 10 end
select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time))


SELECT CONVERT(VARCHAR(8), DATEADD(m, DATEDIFF(m, '', getdate()), ''), 112) AS [月初]
select CONVERT(VARCHAR(8), DATEADD(m, -3, getdate()), 112) AS [三個月前]