CURRENT_TIMESTAMP AS [CURRENTDATE],

[转载]sql datepart ,dateadd,datediff,DateName函数

 (2011-09-13
15:16:56)

图片 1转载

标签: 

转载

分类: SQL语句(oracle,sql)

详细!!

原稿地址:sql datepart ,dateadd,datediff,DateName函数作者:蓝天碧海

  1. 日前系统日期、时间
    select getdate()

  2. dateadd 在向钦点日期加上一段时间的底蕴上,重返新的 datetime 值
    比如说:向日期加上2天
    select dateadd(day,2,’2004-10-15′) –返回:2004-10-17 00:00:00.000

  3. datediff 重回跨四个钦赐日期的日子和时间界限数。
    select datediff(day,’2004-09-01′,’2004-09-18′) –返回:17

  4. datepart 重返代表内定日期的内定日期部分的卡尺头。
    SELECT DATEPART(month, ‘2004-10-15’) –返回 10

  5. datename 再次回到代表钦命日期的钦点日期部分的字符串
    SELECT datename(weekday, ‘2004-10-15’) –返回:星期五

  6. day(), month(),year() –能够与datepart对照一下

select 当明日期=convert(varchar(10),getdate(),120)
,当前岁月=convert(varchar(8),getdate(),114)

select datename(dw,’2004-10-15′)

select 上年第多少周=datename(week,’二零零四-10-15′)
,后天是周几=datename(weekday,’二零零零-10-15′)
函数 参数/功能
GetDate( ) 重临系统当下的日期与时光
DateDiff (interval,date1,date2) 以interval 钦赐的艺术,重临date2
与date1几个日子之间的差值 date2-date1
DateAdd (interval,number,date)
以interval内定的法子,加上number之后的日子
DatePart (interval,date)
再次回到日期date中,interval钦点部分所对应的整数值
DateName (interval,date)
重回日期date中,interval钦命部分所对应的字符串名称

参数 interval的设定值如下:
值 缩 写(Sql Server) Access 和 ASP   说明
Year       Yy
                yyyy   年 1753 ~ 9999
Quarter       Qq     q   季 1 ~ 4
Month       Mm m   月1 ~ 12
Day of year   Dy y    一年的日数,一年中的第几日 1-366
Day       Dd d    日,1-31
Weekday       Dw w    十四日的日数,一周中的第几日 1-7
Week       Wk ww    周,一年中的第几周 0 ~ 51
Hour       Hh h    时0 ~ 23
Minute       Mi n    分钟0 ~ 59
Second       Ss s    秒 0 ~ 59
Millisecond   Ms –    毫秒 0 ~ 999

access 和 asp
中用date()和now()获得系统日期时间;个中DateDiff,DateAdd,DatePart也同是能用来Access和asp中,那几个函数的用法也近乎

举例:
1.GetDate() 用于sql server :select GetDate()

2.DateDiff(‘s’,’二零零五-07-20′,’二〇〇七-7-25 22:56:32′)重回值为 514592 秒
DateDiff(‘d’,’2006-07-20′,’2007-7-25 22:56:32′)再次回到值为 5 天

3.DatePart(‘w’,’二〇〇六-7-25 22:56:32′)重返值为 2
即礼拜一(周天为1,星期六为7)
DatePart(‘d’,’二零零五-7-25 22:56:32′)重回值为 25即25号
DatePart(‘y’,’二零零五-7-25 22:56:32′)重临值为 206即这个时候中第206天
DatePart(‘yyyy’,’二零零七-7-25 22:56:32′)再次回到值为 2007即2007年

SQL Server DATEPART() 函数重返 SQLServer datetime 字段的一局地。

SQL Server DATEPART() 函数的语法是:
DATEPART(portion, datetime)
内部 datetime 是 SQLServer datetime 字段和一些的称号是下列之一: Ms for
Milliseconds
Yy for Year
Qq for Quarter of the Year
Mm for Month
Dy for the Day of the Year
Dd for Day of the Month
Wk for Week
Dw for the Day of the Week
Hh for Hour
Mi for Minute
Ss for Second

详见的印证:

常常,你需求取妥当前几日期和计量一些任何的日子,比如,你的程序只怕要求判别一个月的第一天依然最终一天。你们当先55%人差非常的少都领悟哪些把日子实行私分(年、月、日等),然后仅仅用分割出来的年、月、日等位居多少个函数中总计出团结所急需的日子!在那篇文章里,小编将报告你怎么着利用DATEADD和
DATEDIFF函数来测算出在您的顺序中可能您要用到的局地不等日期。
在行使本文中的例子此前,你必需注意以下的难点。半数以上或许不是兼具例子在差异的机械上实行的结果可能分化样,那完全由哪天是多少个礼拜的第一天这些设置决定。第一天(DATEFI中华VST)设定调整了你的种类应用几时作为21日的率后天。全数以下的例证都以以周天同日而语一周的首后天来创设,也等于首先天设置为7。倘使你的率后天设置不平等,你或者须求调治那个事例,使它和不一致的首后天设置相适合。你能够经过@@DATEFI奥迪Q5ST函数来检查第一天设置。

为了理解那一个事例,我们先复习一下
DATEDIFF和DATEADD函数。DATEDIFF函数计算四个日子之间的钟点、天、周、月、年等时间距离总量。DATEADD函数总计二个日期通过给时间间隔加减来获得贰个新的日期。要掌握越多的DATEDIFF和DATEADD函数以及时光间隔能够翻阅微软联机支持。

动用
DATEDIFF和DATEADD函数来测算日期,和自然从此时此刻日子转变来你必要的日期的设想方法某些不一致。你无法不从岁月间隔这几个地方来设想。比方,从当明天期到您要博取的日子之间有多少日子距离,可能,从明天到某一天(比方一九零一-1-1)之间有微微日子间隔,等等。精通什么着重于大运输距离离有利于你轻轻巧松的知情我的例外的日子计算例子。

一个月的首先天

首先个例子,笔者将报告您怎么着从此时此刻日子去前段时间的结尾一天。请留神:那么些事例以及那篇文章中的其余例子都将只利用DATEDIFF和DATEADD函数来测算大家想要的日期。每二个例证都将经过测算但前的时刻间隔,然后进行加减来获得想要总括的日期。

那是持筹握算叁个月第一天的SQL 脚本:
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

咱俩把这些讲话分开来拜谒它是何等职业的。最大旨的函数是getdate(),超越约得其半人都知晓那几个是回去当前的日期和时间的函数。下三个实践的函数
DATEDIFF(mm,0,getdate())是总括当前几天子和“一九〇一-01-01
00:00:00.000”那些日子之间的月数。记住:时代和时间变量和阿秒同样是从“一九〇五-01-01
00:00:00.000”开首推测的。那正是干什么你能够在DATEDIFF函数中钦点第一个时刻表明式为“0”。下叁个函数是DATEADD,扩张超越天子到“一九〇四-01-01”的月数。通过扩充预约义的日期“1904-01-01”和近期些天期的月数,大家得以得到后一个月的率先天。其他,总括出来的日期的年月部分将会是“00:00:00.000”。

本条总结的工夫是先计算抢先天期到“一九〇四-01-01”的年月间隔数,然后把它加到“一九〇三-01-01”上来博取特殊的日期,那些技术能够用来总结比很多例外的日子。下一个例子也是用那几个技术从此时此刻日子来发出区别的日子。

本周的周一

此间作者是用周(wk)的年月距离来计算曾几何时是本周的星期五。

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

一年的首后天

以后用年(yy)的日子距离来展现那一年的率后天。

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

季度的首后天

即使你要计算这么些季度的首后天,那些事例告诉您该怎么办。

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

当日的半夜三更

现已必要经过getdate()函数为了重回时间值截掉时间部分,就能够思考到眼今天子是或不是在半夜三更。假诺那样,那几个事例使用DATEDIFF和DATEADD函数来得到半夜三更的时间点。

SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

深切DATEDIFF和DATEADD函数总结

你可以知道,通过行使简便的DATEDIFF和DATEADD函数总结,你能够发掘比相当多比不上的恐怕有含义的日子。

如今停止的保有例子只是单纯总计当前的时日和“一九〇一-01-01”之间的时日距离数量,然后把它加到“1902-01-01”的日子间隔上来计量出日期。假定你改改时间间隔的数量,恐怕使用不一样的年华距离来调用DATEADD函数,或然收缩时间间隔实际不是增添,那么通过这一个小的调动你能够发掘和多不一致的日子。

此间有多个例证使用另外叁个DATEADD函数来测算最后一天来分别替换DATEADD函数前后两个小时间隔。

上月的终极一天

那是七个乘除上个月最终一天的例证。它经过从三个月的尾声一天这几个事例上降低3飞秒来博取。有少数要记住,在Sql
Server中时间是可信到3皮秒。那正是干什么自个儿急需缩短3阿秒来获得作者要的日子和岁月。

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

计算出来的日期的小时部分含有了三个Sql
Server能够记下的一天的最后每一天(“23:59:59:997”)的时刻。

二零一八年的末梢一天

一而再上边包车型客车例子,为了要收获二〇一八年的最后一天,你必要在当年的第一天上收缩3飞秒。

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

上月的末尾一天

现行,为了赢得前段时间的终极一天,小编急需多少修改一下拿走这段时间的末段一天的语句。修改需求给用DATEDIFF相比较当今天子和“一九零四-01-01”再次来到的岁月间隔上加1。通过加1个月,作者总计出上个月的首先天,然后减去3皮秒,那样就总括出了本月的最后一天。那是总计本月最终一天的SQL脚本。

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)

在那些例子里,小编动用了“本周的周二”的脚本,并作了一丝丝修改。修改的局部是把原先剧本中“getdate()”部分替换到总结上个月的第6天,在估测计算中用前段时间的第6天来替换当今天子使得总结可以收获上一个月的率先个周一。

总结

自家希望那个事例能够在您用DATEADD和DATEDIFF函数总结日期时给您或多或少启迪。通过使用那一个总计日期的时光间隔的数学方法,小编发觉为了显得五个日子之间间隔的有用历法是有价值的。注意,那只是估测计算出这一个日子的一种方法。要切记,还有多数办法能够获取一致的估摸结果。假若你有其它的情势,那很不利,若是你未有,笔者梦想这么些事例能够给你有的启迪,当您要用DATEADD和DATEDIFF函数计算你程序或者要用到的日期时。


附录,其余日期管理格局

1)去掉时分秒
declare @ datetime
set @ = getdate() –‘2003-7-1 10:00:00’
SELECT @,DATEADD(day, DATEDIFF(day,0,@), 0)

2)呈现星期几
select datename(weekday,getdate())

3)怎样获取有些月的天命
declare @m int
set @m=2 –月份
select datediff(day,’2003-‘+cast(@m as varchar)+’-15′ ,’2003-‘+cast(@m+1
as varchar)+’-15′)
其余,猎取前段时期运气
select datediff(day,cast(month(GetDate()) as
varchar)+’-‘+cast(month(GetDate()) as varchar)+’-15′
,cast(month(GetDate()) as varchar)+’-‘+cast(month(GetDate())+1 as
varchar)+’-15′)
抑或利用总结前段日子的结尾一天的脚本,然后用DAY函数区最终一天
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))

4)剖断是还是不是闰年:
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

5)三个季度多少天
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))

一、MySQL查询前几天、昨日、上周、近30天、二〇一八年等的数额的章程:

DATEADD(month, DATEDIFF(month, ‘一九九九1231’, orderdate), ‘19971231’)
中间是增多的月数  末了一个从什么时间初叶加

–SELECT

 

多少个标准化 0,1 默许排序规则 通过int 对相应的标准实行排序

N表示National,用于表示字符串是Unicode数据类型(NCHAEvoque或NVARCHA瑞鹰).

0、Sql server中DateDiff()用法



 

DATEDIFF 函数 [日期和岁月]

功能 
回到七个日子之间的间隔。

语法 
DATEDIFF ( date-part, date-expression-1, date-expression-2 )

date-part :
year | quarter | month | week | day | hour | minute | second |
millisecond

参数 
date-part    钦命要度量其距离的日子部分。

关于日期部分的详细音讯,请参见日期部分。

date-expression-1    某一间距的起初日期。从 date-expression-2中减去该值,再次回到多个参数之间 date-parts 的天命。

date-expression-2    某一区间的了断日期。从该值中减去
Date-expression-1,重临五个参数之间 date-parts 的气数。

用法 
此函数计算八个钦定日期之间日期部分的数据。结果为日期部分中也就是(date2 –
date1)的有号子的整数值。

当结果不是日期部分的偶好数倍时,DATEDIFF 将被截断实际不是被舍入。

当使用 day 作为日期部分时,DATEDIFF
再次来到几个钦定的岁月之间(包涵第二个日子但不包含率先个日子)的上午数。

当使用 month 作为日期部分时,DATEDIFF
再次来到八个日子之间(饱含第二个日子但不蕴含率先个日子)出现的月的第一天的数码。

当使用 week 作为日期部分时,DATEDIFF
重回七个日子(富含第三个日子但不包蕴率先个日子)之间周末的数目。

对于更加小的光阴单位存在溢出值:

milliseconds    24 天

seconds    68 年

minutes    4083 年

others    未有溢出限制

只要当先这一个限制,此函数将再次回到溢出荒唐。

正规和包容性 
SQL/92    Transact-SQL 扩展。

SQL/99    Transact-SQL 扩展。

Sybase    与 Adaptive Server Enterprise 兼容。

上边示例的口舌重返 1:

SELECT datediff( hour, ‘4:00AM’, ‘5:50AM’ )上面包车型客车讲话重返 102:

SELECT datediff( month, ‘一九八七/05/02’, ‘一九九五/11/15’ )上边包车型地铁言语再次回到 0:

SELECT datediff( day, ’00:00′, ’23:59′ )上边包车型地铁讲话重临 4:

SELECT datediff( day,
   ‘1999/07/19 00:00’,
   ‘一九九七/07/23 23:59’ )上面包车型客车口舌重返 0:

SELECT datediff( month, ‘1997/07/19’, ‘一九九九/07/23’ )上边的讲话再次来到 1:

SELECT datediff( month, ‘1999/07/19’, ‘1999/08/23’ )

  1. 姓氏a上有相同的 

    SELECT empid, firstname, lastname
    FROM HR.Employees
    WHERE lastname LIKE ‘%a%a%’;

–orderid,

 
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = DATEADD(month, DATEDIFF(month, '19991231', orderdate), '19991231');

将字符串文字转成日期类型时,若无一些名时间,sqlserver则暗许用上午日子作为其时间值.

去除建马上间超越3天的订单记录

 

delete 订单表 where datediff( dd, order_addtime, getdate() ) > 3
用函数datediff() datediff( dd, 时间1, 时间2
),意思是:总括时间1到时刻2里面包车型地铁造化 所以,datediff( dd,
order_addtime, getdate() ) > 3,正是超过3天的

今天  

select * from 表名 where to_days(时间字段名) = to_days(now());  
昨天  
SELECT * FROM 表名 WHERETO_DAYS(NOW( ) ) – TO_DAYS(
时间字段名) <= 1  
7天  
SELECT * FROM 表名 whereDATE_SUB(CURDATE(), INTERVAL 7 DAY)
<=date(时间字段名)  
近30天  
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY)
<=date(时间字段名)  
本月  
SELECT * FROM 表名 WHEREDATE_FORMAT( 时间字段名, ‘%Y%m’ )
=DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )  
上一月  
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ )
, date_format( 时间字段名, ‘%Y%m’ ) ) =1  
#询问前一季度数据  
select * from `ht_invoice_information`
whereQUARTER(create_date)=QUARTER(now());  
#询问上季度数据  
select * from `ht_invoice_information` where
QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));  
#询问二零二零年数据  
select * from `ht_invoice_information` where
YEAR(create_date)=YEAR(NOW());  
#询问后年数据  
select * from `ht_invoice_information` where
year(create_date)=year(date_sub(now(),interval 1 year));  
查询当前前一周的数码   
SELECT name,submittime FROM enterprise WHERE
YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now());  
查询前一周的多少  
SELECT name,submittime FROM enterprise
WHEREYEARWEEK(date_format(submittime,’%Y-%m-%d’))
=YEARWEEK(now())-1;  
查询当前月份的数据  
select name,submittime from enterprise   where
date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’)  
询问距离当前现行反革命四个月的数码  
select name,submittime from enterprise where submittime between
date_sub(now(),interval 6 month) and now();  
询问后一个月的多少  
select name,submittime from enterprise   where
date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(),
INTERVAL 1 MONTH),’%Y-%m’)  
select * from ` user ` where DATE_FORMAT(pudate, ‘ %Y%m ‘ ) =
DATE_FORMAT(CURDATE(), ‘ %Y%m ‘ ) ;  
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’))
= WEEKOFYEAR(now())  
select *   
from user
where MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())  
select *   
from [ user ]   
where YEAR (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = YEAR (now())  
and MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())  
select *   
from [ user ]   
where pudate between 上月最终一天  
and 前些时间先是天  
where date(regdate)   =   curdate();  
select   *   from   test   where year(regdate)=year(now())   and
month(regdate)=month(now())   and day(regdate)=day(now())  
SELECT date( c_instime ) ,curdate( )  
FROM `t_score`  
WHERE 1  
LIMIT 0 , 30 

二、相关函数简介

 

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = EOMONTH(orderdate);

 

SELECT custid, region
FROM Sales.Customers
ORDER BY
  CASE WHEN region IS NULL THEN 1 ELSE 0 END, region;

 

 使用EMONTH 对输入的日期重临月末日期   类似动态条件

EXEC sp_helpconstraint N’SCORE’

4.准绳筛选case

RANK代表后面有微微行更低的排序值,而DENSE
RANK表示前面某些许个分歧的排序值。

三种写法  记住 能够用动态条件 区分记录

ORDER BY

3.对各种客商的订单日期排序

RANK 和 DENSE_RANK为持有同等逻辑排序值的具备行生成同样的排名。

第一种 是指向找出  固定的针对性  第三种没有得以多字段条件

SYSDATETIME() as [sysdatetime],

DATEDIFF(month, ‘一九九九1231’, orderdate)  相差多少月 
从19971231到 orderdate之间先查多少月

–select

SELECT custid, orderdate, orderid,
  ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders
ORDER BY custid, rownum;

能够选用Replace来总计某字符在字符串中出现的次数,方法是先把它替换来空,然后比对前后的差值再除以要协作的字符串的长度就能够了。

SELECT empid, firstname, lastname, titleofcourtesy,
  CASE titleofcourtesy
    WHEN 'Ms.'  THEN 'Female'
    WHEN 'Mrs.' THEN 'Female'
    WHEN 'Mr.'  THEN 'Male'
    ELSE             'Unknown'
  END AS gender
FROM HR.Employees;

SELECT empid, firstname, lastname, titleofcourtesy,
CASE 
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;

SELECT

5.在order by 加限制排序条件

cast是ANSI的标准SQL.

1.再次来到 每月最终一天订单

在大部状态下,在过滤条件中对列进行函数化管理,极有望引致相应列上的目录无法利用,降低查询成效,施工方案是运用等值的范围查询.举例:

 

本内容是自己读书tsql二〇〇八的读书笔记

 

 

变长字符的存款和储蓄空间由实际数据量来决定,不过创新数据的时候只怕会必要多少移动,超过当前页的限定,所以更新时的效能相对会低一些。

–orderid,

–empid

–FROM