SELECT DATEDIFF(yy,'2001-1-1',getdate()); 表示當(dāng)前時(shí)間和2001-1-1相差多少年陆淀；
SELECT DATEADD(yy,1,getdate())童太；往當(dāng)前時(shí)間上加一年缭嫡；//顯示的是2020-04-01 11:22:57.543
SELECT DateName(year,GetDate()) as '年',得到當(dāng)前年派殷；
DateName(month,GetDate()) as '月',
DateName(day,GetDate()) as '日',
DateName(dw,GetDate()) as '星期',
DateName(week,GetDate()) as '周數(shù)',
DateName(hour,GetDate()) as '時(shí)',
DateName(minute,GetDate()) as '分',
DateName(second,GetDate()) as '秒'
1.顯示本月第一天
SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
select convert(datetime,convert(varchar(8),getdate(),120)+'01',120)
2.顯示本月最后一天
select dateadd(day,-1,convert(datetime,convert(varchar(8),dateadd(month,1,getdate()),120)+'01',120))
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
3.上個(gè)月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
4.本月的第一個(gè)星期一
select DATEADD(wk,DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())),0)
5.本年的第一天
SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
6.本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))
7.去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
8.本季度的第一天
SELECT DATEADD(qq,DATEDIFF(qq,0,getdate()),0)
9.本周的星期一
SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
10.查詢本月的記錄
select * from tableName where DATEPART(mm, theDate) = DATEPART(mm, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
11.查詢本周的記錄
select * from tableName where DATEPART(wk, theDate) = DATEPART(wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
12.查詢本季的記錄
select * from tableName where DATEPART(qq, theDate) = DATEPART(qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
其中:GETDATE()是獲得系統(tǒng)時(shí)間的函數(shù)偿凭。
13.獲取當(dāng)月總天數(shù):
select DATEDIFF(dd,getdate(),DATEADD(mm, 1, getdate()))
select datediff(day,
dateadd(mm, datediff(mm,'',getdate()), ''),
dateadd(mm, datediff(mm,'',getdate()), '1900-02-01'))
14.獲取當(dāng)前為星期幾
DATENAME(weekday, getdate())
- 當(dāng)前系統(tǒng)日期睁壁、時(shí)間
select getdate()
- dateadd 在向指定日期加上一段時(shí)間的基礎(chǔ)上氏身，返回新的 datetime 值
例如：向日期加上2天
select dateadd(day,2,'2015-08-15') --返回：2015-08-17 00:00:00.000
- datediff 返回跨兩個(gè)指定日期的日期和時(shí)間邊界數(shù)。
select datediff(day,'2015-08-01','2015-08-17') --返回：16
