本文共 3190 字,大约阅读时间需要 10 分钟。
dateadd(datepart,number,
date
)
datepart :
year
(yy, yyyy) | quarter(qq,q) |
month
(mm,m) | week(ww,wk) |
day
(dd,dy) |
hour
(h) |
minute
(mi,n) |
second
(ss,s)| millisecond(ms)
date
参数是合法的日期表达式。number 是您希望添加的间隔数;对于未来的时间,此数是正数,对于过去的时间,此数是负数。
select
dateadd(
year
,1,getdate())
select
dateadd(
month
,1,getdate())
select
dateadd(
day
,1,getdate())
select
dateadd(week,1,getdate())
select
dateadd(weekday,1,getdate())
select
dateadd(
hour
,1,getdate())
select
dateadd(
Minute
,1,getdate())
select
dateadd(
Second
,1,getdate())
datediff(
date
-part, startdate, enddate )
datepart :
year
(yy, yyyy) | quarter(qq,q) |
month
(mm,m) | week(ww,wk) |
day
(dd,dy) |
hour
(h) |
minute
(mi,n) |
second
(ss,s)| millisecond(ms)
startdate 和 enddate 参数是合法的日期表达式。
返回值:(enddate)-(startdate)
select
datediff(
year
,getdate(),dateadd(
year
,2,getdate()))
SELECT
*
FROM
crm_contract
where
datediff(
month
,Subtime,getdate())=0
SELECT
DATEPART(
year
, getdate())
as
'年份'
SELECT
DATEPART(
month
, getdate())
as
'月份'
SELECT
DATEPART(
day
, getdate())
as
'日期'
SELECT
DATEPART(week, getdate())
as
'本年第几周数'
SELECT
(DATEPART(weekday, getdate())-1)
as
'今天是周几?'
SELECT
DATEPART(
hour
, getdate())
as
'小时'
SELECT
DATEPART(
Minute
, getdate())
as
'分钟'
SELECT
DATEPART(
Second
, getdate())
as
'秒时'
SELECT
datename(weekday, getdate())
as
'今天是星期几?'
SELECT
datename(
month
,
convert
(
varchar
(10), getdate(),126))
as
'今天是几月份?'
SELECT
datename(
day
,
convert
(
varchar
(10), getdate(),126))
as
'今天是几号?'
SELECT
datename(
year
,
convert
(
varchar
(10), getdate(),126))
as
'今年是哪年?'
SELECT
DATEADD(wk, DATEDIFF(wk,0,getdate()),-7)
SELECT
DATEADD(wk, DATEDIFF(wk,0,getdate()),-1)
SELECT
DATEADD(wk, DATEDIFF(wk,0,getdate()),0)
select
dateadd(wk,datediff(wk,0,getdate()),6)
select
dateadd(wk,datediff(wk,0,getdate()),7)
select
dateadd(wk,datediff(wk,0,getdate()),13)
SELECT
DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
SELECT
dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
SELECT
DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
select
dateadd(wk,datediff(wk,0,getdate()),6)
SELECT
DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
SELECT
dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 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))
DATEDIFF(wk,0,dateadd(dd,6-datepart(
day
,getdate()),getdate())), 0)
转载于:https://www.cnblogs.com/Li-yuan/p/6180391.html