您的位置:新葡亰496net > 网络数据库 > 日子管理datetime和date之间的相互转变,server中的

日子管理datetime和date之间的相互转变,server中的

发布时间:2019-10-05 12:48编辑:网络数据库浏览(67)

    DATEADD   在向指定日期加上一段时间的基础上,返回新的 datetime 值。

    日期处理datetime和date之间的相互转换

    DATEPART  
      返回代表指定日期的指定日期部分的整数。  
       
      语法  
      DATEPART   (   datepart   ,   date   )    
       
      参数  
      datepart  
       
      是指定应返回的日期部分的参数。下表列出了   Microsoft®   SQL   Server™   识别的日期部分和缩写。  
       
      日期部分   缩写    
      year   yy,   yyyy    
      quarter   qq,   q    
      month   mm,   m    
      dayofyear   dy,   y    
      day   dd,   d    
      week   wk,   ww    
      weekday   dw    
      Hour   hh    
      minute   mi,   n    
      second   ss,   s    
      millisecond   ms    
       
       
      week   (wk,   ww)   日期部分反映对   SET   DATEFIRST   作的更改。任何一年的   1   月   1   日定义了   week   日期部分的开始数字,例如:DATEPART(wk,   'Jan   1,   xxxx')   =   1,此处   xxxx   代表任一年。  
       
      weekday   (dw)   日期部分返回对应于星期中的某天的数,例如:Sunday   =   1、Saturday   =   7。weekday   日期部分产生的数取决于   SET   DATEFIRST   设定的值,此命令设定星期中的第一天。  
       
      date  
       
      是返回   datetime   或   smalldatetime   值或日期格式字符串的表达式。对   1753   年   1   月   1   日之后的日期用datetime   数据类型。更早的日期存储为字符数据。当输入   datetime   值时,始终将其放入引号中。因为   smalldatetime   只精确到分钟,所以当用   smalldatetime   值时,秒和毫秒总是   0。  
       
      如果只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如,如果   two   digit   year   cutoff   为   2049   (默认),则   49   被解释为   2049,2050   被解释为   1950。为避免模糊,请使用四位数的年份。  
       
      有关时间值指定的更多信息,请参见时间格式。有关日期指定的更多信息,请参见   datetime   和   smalldatetime。    
       
      返回类型  
      int  
       
      注释  
      DAY、MONTH、和   YEAR   函数分别是   DATEPART(dd,   date)、DATEPART(mm,   date)、和   DATEPART(yy,   date)   的同义词。  
       
    日子管理datetime和date之间的相互转变,server中的日期函数。  示例  
      GETDATE   函数返回当前日期;然而,比较时并不总是需要完整的日期信息(通常只是对日期的一部分进行比较)。此示例显示   GETDATE   及   DATEPART   的输出。        

    DATEPART

    返回代表指定日期的指定日期部分的整数。

    语法           DATEADD ( datepart , number, date )

    日期是数据处理中经常使用到的信息之一。生日、数据处理时间、计划的预计完成时间,按年、季、月的统计,这些都属于日期处理的范畴。由于日期中包含了年、季、月、日等众多信息,不同的国家对日期格式、日期文字描述及星期有不同的规定,因此产生了日期处理的复杂性。本章主要讨论在SQL Server数据库中对日期的各种处理方法。

    图片 1  SELECT   GETDATE()   AS   'Current   Date'   
    图片 2  GO   

    语法

    DATEPART ( datepart ,date )

    参数           

    日期类型概述

    SQL Server中的日期类型包括datetime和smalldatetime,仅能处理可以识别为1753年~9999年间的日期的值,没有单独的日期型或时间型。

        
      下面是结果集:        

    参数

    datepart

    是指定应返回的日期部分的参数。下表列出了 Microsoft® SQL Server™ 识别的日期部分和缩写。

    日期部分 缩写
    year yy, yyyy
    quarter qq, q
    month mm, m
    dayofyear dy, y
    day dd, d
    week wk, ww
    weekday dw
    Hour hh
    minute mi, n
    second ss, s
    millisecond ms

     

    week (wk, ww) 日期部分反映对 SET DATEFIRST 作的更改。任何一年的 1 月 1 日定义了 week 日期部分的开始数字,例如:DATEPART(wk, 'Jan 1, xxxx') = 1,此处 xxxx 代表任一年。

    weekday (dw) 日期部分返回对应于星期中的某天的数,例如:Sunday = 1、Saturday = 7。weekday 日期部分产生的数取决于 SET DATEFIRST 设定的值,此命令设定星期中的第一天。

    date

    是返回 datetimesmalldatetime 值或日期格式字符串的表达式。对 1753 年 1 月 1 日之后的日期用datetime 数据类型。更早的日期存储为字符数据。当输入 datetime 值时,始终将其放入引号中。因为 smalldatetime 只精确到分钟,所以当用 smalldatetime 值时,秒和毫秒总是 0。

    如果只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如,如果 two digit year cutoff 为 2049 (默认),则 49 被解释为 2049,2050 被解释为 1950。为避免模糊,请使用四位数的年份。

    有关时间值指定的更多信息,请参见时间格式。有关日期指定的更多信息,请参见 datetime 和 smalldatetime。

    (1) datepart:是规定应向日期的哪一部分返回新值的参数。

    1.datetime

    datetime类型处理从1753年1月1日~9999年12月31日的日期和时间数据,精确度为百分之三秒。即:对于0.000~0.001、0.009的日期值,调整为0.000;对于0.002~0.004的日期值,调整为0.003;对于0.005~0.008的日期值,调整为0.007。

    例如,下面的代码在输入时,其时间精确度为百分之一秒,但经数据库保存后再显示出来,其结果就已经做了处理。

    DECLARE @t TABLE(date char(21))

    INSERT @t SELECT '1900-1-1 00:00:00.000'

    ...

    INSERT @t SELECT '1900-1-1 00:00:00.009'

    SELECT date,转换后的日期=CAST(date as datetime) FROM @t

     

    /*--结果

    date                     转换后的日期



    1900-1-1 00:00:00.000    1900-01-01 00:00:00.000
    ...
    1900-1-1 00:00:00.000    1900-01-01 00:00:00.010

    --*/

    datetime的存储长度为8字节,日期和时间各用4个字节存储,第一个4字节存储自1900年1月1日之前或之后的天数(以1900年1月1日为分界点,在1900年1月1日之前的日期的天数小于0,在1900年1月1日之后的日期的天数大于0)。另外一个4字节存储以午夜(00:00:00.000)后毫秒数所代表的每天的时间。

    例如,下面的代码演示了datetime变量中,仅包含单纯的日期和单纯的时间时,日期存储的十六进制存储表示结果。

    DECLARE @dt datetime

     

    --单纯的日期

    SET @dt='1900-1-2'

    SELECT CAST(@dt as binary(8))

    --结果: 0x0000000100000000

     

    --单纯的时间

    SET @dt='00:00:01'

    SELECT CAST(@dt as binary(8))

    --结果: 0x000000000000012C

    图片 3  Current   Date                                   
    图片 4  ---------------------------     
    图片 5  Feb   18   1998   11:46PM                     
    图片 6    
    图片 7  SELECT   DATEPART(month,   GETDATE())   AS   'Month   Number'   
    图片 8  GO   

    返回类型

    int

    下表列出了 Microsoft® SQL Server™ 识别的日期部分和缩写。
    日期部分 缩写
    Year yy, yyyy
    quarter qq, q
    Month mm, m
    dayofyear dy, y
    Day dd, d
    Week wk, ww
    Hour hh
    minute mi, n
    second ss, s
    millisecond ms

    2.smalldatetime

    smalldatetime类型处理从1900年1月1日~2079年6月6 日的日期和时间数据,精确到分钟。29.998秒或更低的smalldatetime值向下舍入为最接近的分钟,29.999秒或更高的smalldatetime值向上舍入为最接近的分钟。

    smalldatetime的存储长度为4字节,第一个2字节存储自1900年1月1日之后的天数。另外一个2字节存储午夜(00:00:00.000)后的分钟数。

    例如,下面的代码演示了smalldatetime变量中,仅包含单纯的日期和单纯的时间时,日期存储的十六进制存储表示结果。

    DECLARE @dt smalldatetime

    --单纯的日期

    SET @dt='1900-1-2'

    SELECT CAST(@dt as binary(4))

    --结果: 0x00010000

    --单纯的时间

    SET @dt='00:10'

    SELECT CAST(@dt as binary(4))
    --结果: 0x0000000A

      下面是结果集:  
       
      Month   Number    
      ------------    
      2                          
       
      此示例假设日期是   5   月   29   日。        

    注释

    DAY、MONTH、和 YEAR 函数分别是 DATEPART(dd, date)、DATEPART(mm, date)、和 DATEPART(yy, date) 的同义词。

    (2)number:是用来增加 datepart 的值。如果指定一个不是整数的值,则将废弃此值的小数部分。

    日期处理函数

    日期由年、月、日、时等多个部分组成,它的处理相对复杂,因此,SQL Server提供了大量的日期处理函数,用以完成各种日期数据的处理。掌握好这些函数,对完成数据库的各种日期处理非常必要,本节将介绍几个常用的日期处理函数。期增减函数可以对日期指定部分的值进行增减,并返回处理后的日期值,SQL Server提供的日期增减函数为DATEADD。

    DATEADD的具体语法如下:DATEADD ( datepart , number, date )

    其中包括以下参数。

    ¡  datepart:是规定应向日期的哪一部分返回新值的参数。表2-1列出了SQL Server支持的日期部分、缩写及含义。

                        DATEADD、DATEDIFF支持的日期部分、缩写及含义

    日 期 部 分

    缩    写

    含    义

    Year

    yy , yyyy

    年份

    Quarter

    qq , q

    季度

    Month

    mm , m

    月份

    Dayofyear

    dy,y

    Day

    dd , d

    Week

    wk , ww

    星期

    Hour

    Hh

    小时

    Minute

    mi , n

    分钟

    Second

    ss , s

    Millisecond

    Ms

    毫秒

     

    ¡  number:是用来增加datepart的值。正数表示增加,负数表示减少,如果指定的是非整数值,则忽略此值的小数部分,不做四舍五入处理。例如,DATEADD(Day,1.7,date),表示date增加1天。

    ¡  date:是返回datetime或smalldatetime值或日期格式字符串的表达式。

    如果date是smalldatetime,则返回smalldatetime,否则返回datetime。date为smalldatetime,Datepart为Second(ss,s)或Millisecond(ms)时,返回值将根据日期增减的结果调整到分钟;date为datetime,Datepart为Millisecond(ms)时,返回值将根据日期增减的结果调整为百分之三秒。调整规则可以参考2.1节的相关说明。

    date允许直接与number进行增减计算,即对于DATEADD(Day,number,date),等同于date number。

    图片 9  SELECT   DATEPART(month,   GETDATE())   
    图片 10  GO   

    示例

    GETDATE 函数返回当前日期;然而,比较时并不总是需要完整的日期信息(通常只是对日期的一部分进行比较)。此示例显示 GETDATE 及 DATEPART 的输出。

    SELECT GETDATE() AS 'Current Date'
    GO
    

    下面是结果集:

    Current Date
    ---------------------------
    Feb 18 1998 11:46PM
    SELECT DATEPART(month, GETDATE()) AS 'Month Number'
    GO
    

    下面是结果集:

    Month Number
    ------------
    2
    

    此示例假设日期是 5 月 29 日。

    SELECT DATEPART(month, GETDATE())
    GO
    

    下面是结果集:

    -----------
    5
    (1 row(s) affected)
    

    在此示例中,以数字表示日期。注意:SQL Server 将 0 解释为 01/01/1900。

    SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)
    

    下面是结果集:

    ----- ------ ------
    1     1      1900
    
    摘自:http://www.cnblogs.com/jackrebel/archive/2008/04/17/1157540.html
    

        例如,如果为 datepart 指定 day,为 number 指定 1.75,则 date 将增加 1。

     日期信息获取函数

    日期信息获取函数用于获取日期指定部分的相关信息,常用的日期信息获取函数如表2-2所示。

                                          常用的日期信息获取函数

    功 能 说 明

    语    法

    参数及返回值数据类型说明

    返回代表指定日期的指定日期部分的字符串

    DATENAME(datepart,date)

    datepart是指定应返回的日期部分的参数,其定义如表2-3所示。date是返回datetime或smalldatetime值或日期格式字符串的表达式。DATENAME函数返回nvarchar,DATEPART函数返回int

    返回代表指定日期的指定日期部分的整数

    DATEPART(datepart,date)

    返回表示指定日期中的年份的整数

    YEAR(date)

    返回int

    返回表示指定日期中的月份的整数

    MONTH(date)

    返回int

    返回表示指定日期中的天的整数

    DAY(date)

    返回int

                      DATENAME、DATEPART支持的日期部分、缩写及含义

    日 期 部 分

    缩    写

    含    义

    Year

    yy , yyyy

    年份

    Quarter

    qq , q

    季度

    Month

    mm , m

    月份

    Dayofyear

    dy , y

    Day

    dd , d

    Week

    wk , ww

    自年初开始的第几个星期

    Weekday

    Dw

    星期几(例如星期一、星期二)

    Hour

    Hh

    小时

    Minute

    mi , n

    分钟

    Second

    ss , s

    秒。date为smalldatetime时,始终返回0

    Millisecond

    Ms

    毫秒。date为smalldatetime时,始终返回0,为datetime时,返回百份之三秒

     

    DATEPART(Week,date)返回的星期计算方式,是按照星期日为一周的第一天,这点与中国人的日期处理习惯不同,在使用时要注意这一点。DATENAME函数返回指定日期的指定日期部分的字符串,其返回的具体字符串值,与SET DATEFIRST及SET DATELANGUAGE选项的设置有关。使用DATEPART(Weekday,date)时,其返回的值与SET DATEFIRST选项的设置有关,具体的将在2.3节中说明。

      下面是结果集:  
       
      -----------    
      5                        
       
      (1   row(s)   affected)  
       
      在此示例中,以数字表示日期。注意:SQL   Server   将   0   解释为   01/01/1900。        

    (3)date:是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。

      日期差值计算函数

    日期差值计算函数用于计算两个给定日期指定部分的边界数,SQL Server提供的日期差值计算函数为DATEDIFF。

    DATEDIFF的具体语法如下:

    DATEDIFF ( datepart , startdate , enddate )

    其中包括以下参数。

    ¡  datepart:规定了应在日期的哪一部分计算差额,其定义如表2-1所示。

    ¡  startdate:规定了计算的开始日期。

    ¡  enddate:规定了计算的终止日期。

    返回类型:integer

    计算的开始日期和终止日期,可以是日期或日期格式的字符串。计算的方法是从enddate减去startdate。如果startdate比enddate晚,返回负值。当结果超出整数值范围,DATEDIFF就产生错误。对于毫秒,最大数是24天20小时31分钟23.647秒。对于秒,最大数是68年。

    计算跨分钟、秒和毫秒这些边界的方法,使得DATEDIFF给出的结果在全部数据类型中是一致的。结果是带正负号的整数值,其等于跨第一个和第二个日期间的datepart边界数。例如,在2005年1月4日和2005年2月11日之间的月份数是1。

    图片 11  SELECT   DATEPART(m,   0),   DATEPART(d,   0),   DATEPART(yy,   0)   

        有关指定日期的更多信息,请参见 datetime 和 smalldatetime。

     其他日期处理相关函数

    其他常用的日期处理相关函数包括以下几个。

      下面是结果集:  
       
      -----   ------   ------  
      1           1             1900  

    如果您只指定年份的最后两位数字,则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如,如果 two digit year cutoff 为 2049(默认),则 49 被解释为 2049,2050 被解释为 1950。为避免模糊,请使用四位数的年份。

    1.GETDATE

    GETDATE按照datetime值返回当前系统日期和时间。

    GETDATE的语法如下:

    GETDATE()

    返回类型:datetime

    返回类型
    返回 datetime,但如果 date 参数是 smalldatetime,返回 smalldatetime。

    2.ISDATE

    ISDATE确定输入的表达式是否有效日期。

    在输入日期表达式时,日期都是以日期格式的字符串提供的,由于不同的区域有不同的日期格式,所以并不能保证输入的日期表达式能够被SQL Server识别,这种情况下,就需要用ISDATE来判断日期表达式能否正确地被SQL Server识别了。

    ISDATE的语法如下:

    ISDATE(expression)

    返回类型:int

     

    3.CONVERT

    CONVERT将某种数据类型的表达式显式转换为另一种数据类型。

    严格来说,CONVERT不属于日期处理函数,只是它被经常用于日期处理中,所以这里把它列入了其他日期处理函数,下面是CONVERT的用法描述(只重点说明在日期处理中的应用)。

    CONVERT的具体语法如下:

    CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

    其中包括以下参数。

    ¡  expression:是要转换数据类型的有效SQL Server表达式。

    ¡  data_type:是expression转换后的数据类型,length是对于有精度定义需要的data_type的精度定义,对于没有精度定义需要的data_type,该参数可以省略。

    ¡  style:定义数据类型转换时的格式,对于日期类型的转换,它的定义如表2-4所示。

    表2-4                                      style在日期转换中的说明

    不带世纪数位

    带世纪数位

    标    准

    输入/输出

    0或100

    默认值

    mon dd yyyy hh:miAM(或 PM)

    1

    101

    美国

    mm/dd/yyyy

    2

    102

    ANSI

    yy.mm.dd

    3

    103

    英国/法国

    dd/mm/yy

    4

    104

    德国

    dd.mm.yy

    5

    105

    意大利

    dd-mm-yy

    6

    106

    dd mon yy

    7

    107

    mon dd, yy

    8

    108

    hh:mm:ss

    9或109

    默认值 毫秒

    mon dd yyyy hh:mi:ss:mmmAM(或PM)

    10

    110

    美国

    mm-dd-yy

    11

    111

    日本

    yy/mm/dd

    12

    112

    ISO

    yymmdd

    13或113

    欧洲默认值 毫秒

    dd mon yyyy hh:mm:ss:mmm(24h)

    14

    114

    hh:mi:ss:mmm(24h)

    20或120

    ODBC规范

    yyyy-mm-dd hh:mm:ss[.fff]

    21或121

    ODBC规范(带毫秒)

    yyyy-mm-dd hh:mm:ss[.fff]

    126

    ISO8601

    yyyy-mm-ddThh:mm:ss.mmm

    130

    Hijri

    dd mon yyyy hh:mi:ss:mmmAM

    131

    Hijri

    dd/mm/yy hh:mi:ss:mmmAM

     

     

    说明:

    ①  输入/输出:“输入”表示从字符串转换为日期时字符串的日期格式,“输出”指从日期转换为字符串时的日期字符串格式。

    ②  Hijri:是具有几种变化形式的日历系统,SQL Server使用其中的科威特算法。

     

    当从smalldatetime转换为字符数据时,由于smalldatetimer只保存到分钟的数据,因此,对于包含秒或毫秒的样式,将在秒或毫秒的位置上显示零。当从datetime或smalldatetime值进行转换时,可以通过使用适当的char或varchar数据类型长度来截断不需要的日期部分。

     

    注意:

    在SQL Server中,由于直接提供的日期均是以日期格式的字符串提供,所以在使用CONVERT进行日期格式转换时,要先把日期格式的字符串转换为日期型,然后才能利用CONVERT进行日期格式转换,否则就变成字符串转换为字符串,此时的style选项是无效的。

     

    返回类型:由参数data_type确定。

    下面是利用CONVERT进行日期转换的简单示例:

    /*== 字符转换为日期时,Style的使用 ==*/

    --1. Style=101时,表示日期字符串为:mm/dd/yyyy格式

    SELECT CONVERT(datetime,'11/1/2003',101)

    --结果:2003-11-01 00:00:00.000

    --2. Style=101时,表示日期字符串为:dd/mm/yyyy格式

    SELECT CONVERT(datetime,'11/1/2003',103)

    --结果:2003-01-11 00:00:00.000

     

    /*== 日期转换为字符串 ==*/

    DECLARE @dt datetime

    SET @dt='2003-1-11'

    --1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式

    SELECT CONVERT(varchar,@dt,101)

    --结果:01/11/2003

    --2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式

    SELECT CONVERT(varchar,@dt,103)

    --结果:11/01/2003

     

    /*== 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 ==*/

    SELECT CONVERT(varchar,'2003-1-11',101)

    --结果:2003-1-11

    例如:在当天的基础上加上一天的时间。

    SELECT DATEADD (day, 1,CONVERT(varchar(100), GETDATE(), 21) )  as time
    

    查询本月若为2017年2月,则下面的语句查询出为0,即为本月。

    select datediff(month,'2017-02-13 00:00:00.000',getdate()) 
    

    查询当天,若当天是2017年2月3日,则返回0,即为当天。

     select datediff(day,'2017-02-13',getdate()) 
    

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:日子管理datetime和date之间的相互转变,server中的

    关键词:

上一篇:有关binlog的那点事,的法力表明

下一篇:没有了