日期类型-DateTime

DateTime是sql中最常用的日期类型。

存储大小为：8个字节；

日期范围：1753-01-01到9999-12-31；

精确度：3.33毫秒；

常用的日期函数

GetDate()获取系统当前日期

select GETDATE()

DatePart(part,dtValue) 获取日期中的一部分,part为要获取的部分，dtValue为日期

part 参数有：

日期部分 缩写 
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

         select GETDATE() as '系统当前日期' ,

                DATEPART(YEAR, GETDATE()) as '年',

                DATEPART(month,GETDATE()) as '月',

                DATEPART(DAY,GETDATE()) as '天',

                DATEPART(HOUR,GETDATE()) as '小时',

                DATEPART(minute,GETDATE()) as '分',

                DATEPART(second,GETDATE()) as '秒',

                DATEPART(Millisecond,GETDATE()) as '毫秒',

                DATEPART(quarter,getdate()) as '季度',

                DATEPART(dayofyear,getdate()) as '从年初到现在的天数',

                DATEPART(weekday,getdate()) as '今天是这个星期的第几天，从星期天开始',

                DATEPART(week,getdate()) as '今年的第几周'

      --part简写

          select GETDATE() as '系统当前日期' ,

                DATEPART(YYYY, GETDATE()) as '年',

                DATEPART(MM,GETDATE()) as '月',

                DATEPART(DD,GETDATE()) as '天',

                DATEPART(HH,GETDATE()) as '小时',

                DATEPART(MI,GETDATE()) as '分',

                DATEPART(SS,GETDATE()) as '秒',

                DATEPART(MS,GETDATE()) as '毫秒',

                DATEPART(qq,getdate()) as '季度' ,

                DATEPART(dy,getdate()) as '从年初到现在的天数',

                DATEPART(dw,getdate()) as '今天是这个星期的第几天，从星期天开始',

                DATEPART(wk,getdate()) as '今年的第几周'

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

  SELECT datename(weekday, '2014-12-8')

  SELECT datename(YY, '2014-12-8')

year(dtValue)，Month(dtValue),Day(dtValue)分别获取日期中：年，月，日的部分

 select year('2014-01-12'),

        Month('2014-01-12'),

        DAY('2014-01-12')                                

DateAdd(part,num,dtValue)对给定的某一日期加减,num为正数则为加，为负数则为减

select    '' as  '给定的日期',

        dateadd(year,2,'') as '加2年' ,

        dateadd(year,-2,'') as '减2年',

        dateadd(month,12,'') as '加12个月',

        dateadd(month,-12,'') as '减12个月',

         dateadd(day,31,'') as '加31天',

        dateadd(day,-31,'') as '减31天'

DateDiff(part,dtValue1.dtValue2)计算2个日期间的间隔

select  datediff(year,'1988-12-21','1990-12-21'),--大的时间在后面 间隔为正数

        datediff(year,'1990-12-21','1988-12-21')--大的时间在前面面 间隔为负数

日期格式

convert(dataType,value[,style])函数可以将表达式由一种数据类型转换为另一种数据类型,而且可以把日期转换为新数据类型的通用函数

Style ID Style 格式
100 或者 0 mon dd yyyy hh:miAM （或者 PM）
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
109 或者 9 mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
113 或者 13 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
120 或者 20 yyyy-mm-dd hh:mi:ss(24h)
121 或者 21 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM 
  select GETDATE(),

                    CONVERT(nvarchar(20),GETDATE(),120) as 'style=120',

                    CONVERT(nvarchar(20),GETDATE(),101) as 'style=101',

                    CONVERT(nvarchar(20),GETDATE(),112) as 'style=112'

顺便学习一下cast(expression as dataType[(length)])函数

cast()和convert()函数很相似，可以将表达式由一种数据类型转换为另一种数据类型，但无法像convert()函数一样设置时间格式

cast()是ANSI标准SQL，除非需要设置格式，否则优先选择cast()转换。

select  CAST('' as  decimal(18, 2)) +11, --字符串转换为decimal

                 convert(decimal(18, 2),'')+11,--字符串转换为decimal

                 ''+11 --sql内部自动转换为int

日期函数应用

求本年第一天的日期

select DATEADD(YY,2,'')

dateadd年份相加时,月份天数时间都会为初始值。可以用这个特性来求出第一天日期。

1.选择一个日期为"中间值",算出今年和这个"中间值"相差多少年。

2.然后用"中间值"加上第一步中的求出相差的年份。

select DATEDIFF(yy,'',getdate()) --1.求出中间值2011和今年相差的年份为多少

select DATEADD(yy,   DATEDIFF(yy,'',getdate()),   '')--2.然后用"中间值"加上第一步中的求出相差的年份。

"中间值"一般写0，写0时这个日期则为默认值'1900-01-01'

select DATEDIFF(yy,0,getdate())

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

select DATEDIFF(yy,'1900-01-01',getdate())

select DATEADD(yy,   DATEDIFF(yy,'1900-01-01',getdate()),   '1900-01-01')

那么就上一年或者明年第一天就只需要在相差的年份后面加减即可

select DATEADD(yy,   DATEDIFF(yy,0,getdate())-1,  0)--上一年开始一天

select DATEADD(yy,   DATEDIFF(yy,0,getdate())+1,  0)--下一年开始一天

select DATEADD(yy,   DATEDIFF(yy,0,''),  0)--2008年开始一天

那么求本年第N天的日期只需要加(N-1)天数即可

select DATEADD(yy,   DATEDIFF(yy,0,getdate()),  0)+(101-1)--本年第101天的日期

那么求本月第一天的日期和第N天的日期，求本星期第一天和第N天的日期只需求改part部分即可

   --本月的第一天

  select DATEADD(mm,   DATEDIFF(mm,0,getdate()),0)

   --本月的第13天

  select DATEADD(mm,   DATEDIFF(mm,0,getdate()),0)+(13-1)

  --本星期的第一天

  select DATEADD(WK,   DATEDIFF(WK,0,getdate()),0)

   --本星期的第三天

  select DATEADD(WK,   DATEDIFF(WK,0,getdate()),0)+(3-1)

求本年最后一天的日期

先求出下一年第一天的日期然后减去3毫秒即可

    select DATEADD(yy,   DATEDIFF(yy,0,getdate())+1,   0)--下一年第一天

    SELECT   dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,getdate())+1,   0))  --得到下一年的开始一天 然后在减去3毫秒

为什么是减3毫秒?别的可以吗?

因为datetime类型的时间精度是3.33毫秒，如果超过该精度会进行近似到0.000，0.003，0.007 秒

    select DATEADD(ms,-1, '2015-01-01 00:00:00.000') as '减1毫秒',

           DATEADD(ms,-2, '2015-01-01 00:00:00.000') as '减2毫秒',

           DATEADD(ms,-3, '2015-01-01 00:00:00.000') as '减3毫秒',

           DATEADD(ms,-4, '2015-01-01 00:00:00.000') as '减4毫秒',

           DATEADD(ms,-5, '2015-01-01 00:00:00.000') as '减5毫秒'

    select DATEADD(ms,-6, '2015-01-01 00:00:00.000') as '减6毫秒',

           DATEADD(ms,-7, '2015-01-01 00:00:00.000') as '减7毫秒',

           DATEADD(ms,-8, '2015-01-01 00:00:00.000') as '减8毫秒',

           DATEADD(ms,-9, '2015-01-01 00:00:00.000') as '减9毫秒',

           DATEADD(ms,-10, '2015-01-01 00:00:00.000') as '减10毫秒'

    select

           DATEADD(ms,-11, '2015-01-01 00:00:00.000') as '减11毫秒',

           DATEADD(ms,-12, '2015-01-01 00:00:00.000') as '减12毫秒',

           DATEADD(ms,-13, '2015-01-01 00:00:00.000') as '减13毫秒',

           DATEADD(ms,-14, '2015-01-01 00:00:00.000') as '减14毫秒',

           DATEADD(ms,-15, '2015-01-01 00:00:00.000') as '减15毫秒'

那么就上一年或者明年第一天就只需要在相差的年份后面加减即可

SELECT   dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0))  --上一年最后一天

SELECT   dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,getdate())+2,   0))--下一年最后一天

SELECT   dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,'')+1,   0))--2008年最后一天

那么求本月最后一天的日期，求本星期最后一天日期只需求改part部分即可

  SELECT   dateadd(ms,-3,DATEADD(mm,   DATEDIFF(mm,0,getdate())+1,  0))    --本月的最后一天  

   SELECT   dateadd(ms,-3,DATEADD(WK,   DATEDIFF(WK,0,getdate())+1,   0))   --本星期的最后一天

求本月的第一个星期一

先来看一下如何得到本星期的星期一

1.今天距离'1900-01-01'相差多少个星期，
2.'1900-01-01'加上相差的星期数（加的天数=星期数*7）。而'1900-01-01'刚好是星期一,所以相加后这天也为星期一

--本星期第一天

select DATEADD(WK,   DATEDIFF(WK,0,getdate()),0) ,DATENAME(WEEKDAY,DATEADD(WK,   DATEDIFF(WK,0,getdate()),0))

那么求本星期的第三天，也就是星期3,值需要将'1900-01-01'改为'1900-01-03'即可

select DATEADD(WK,   DATEDIFF(WK,'1900-01-03',getdate()),'1900-01-03') ,DATENAME(WEEKDAY,DATEADD(WK,   DATEDIFF(WK,'1900-01-03',getdate()),'1900-01-03'))

那么求本月的第一个星期一

1.求出当月第一个星期的某一天距离'1900-01-01'相差多少个星期
2.'1900-01-01'加上相差的星期数（加的天数=星期数*7）。而'1900-01-01'刚好是星期一,所以相加后这天也为星期一

这个某一天需要瞒足1个条件---某一天必须和星期一同在一个星期内。

 1号       2号      3号     4号      5号       6号       7号

 星期日    星期1    星期2   星期3    星期4     星期5      星期6  -- 都和星期一在同一个星期

 星期1     星期2    星期3   星期4    星期5     星期6      星期日 --这个地方7号 淘汰了 有星期一但是7号为第2个星期

 星期2     星期3    星期4   星期5    星期6     星期日     星期1  --这个地方 1,2,3,4,5 淘汰了 不和当月第一个星期一在同一个星期内

 星期3     星期4    星期5   星期6    星期日    星期1      星期2  -- 1,2，3,4 淘汰了 不和当月第一个星期一在同一个星期内

 星期4     星期5    星期6   星期日   星期1     星期2      星期3  -- 1,2，3淘汰了 不和当月第一个星期一在同一个星期内

 星期5     星期6    星期日  星期1    星期2     星期3      星期4  -- 1,2淘汰了 不和当月第一个星期一在同一个星期内

 星期6     星期日   星期1   星期2    星期3     星期4      星期5   ---1 淘汰了 不和当月第一个星期一在同一个星期内

可以看出每个月的6号始终都和星期一同在一个星期内。

注意sqlserver中每个星期的第一天是星期日

 select datename(week,'1900-01-01'),DATENAME(WEEKDAY,'1900-01-01')

 select datename(week,'1900-01-01'),DATENAME(WEEKDAY,'1900-01-06')

 select datename(week,'1900-01-07'),DATENAME(WEEKDAY,'1900-01-07')

本月的第一个星期一

--获取本月的6号

select dateadd(dd,6-datepart(day,getdate()),getdate())

--获取本月的6号距离'1900-01-01'相差多少个星期

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

--'1900-01-01'加上相差的星期数（加的天数=星期数*7）。而'1900-01-01'刚好是星期一,所以相加后这天也为星期一

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

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

本月第1个星期三

select  DATEADD(wk,DATEDIFF(wk,'1900-01-03',dateadd(dd,6-datepart(day,getdate()),getdate())),'1900-01-03'),

                    DATENAME(WEEKDAY,DATEADD(wk,DATEDIFF(wk,'1900-01-03',dateadd(dd,6-datepart(day,getdate()),getdate())),'1900-01-03'))

那么本月第2个星期一

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

            DATENAME(WEEKDAY,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,13-datepart(day,getdate()),getdate())),0))

其他

 select  dateadd(day,2-datepart(weekday,getdate()),convert(varchar,getdate(),112))  --获取本周星期一

 select dateadd(day,9-datepart(weekday,getdate()),convert(varchar,getdate(),112))   --获取下周星期一

 select dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))  --获取本月1号

 select dateadd(month,1,dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))) --获取下个月一号

 select dateadd(month,-1,dateadd(day,1-day(getdate()),convert(varchar,getdate(),112)))  --获取上个月一号

最新文章

  1. iOS多线程杂论
  2. .NET开发 正则表达式中的 Bug
  3. poj3026（bfs+prim）
  4. SQL中存储过程和自定义函数的区别（转载）
  5. Swift - 闭包的介绍及用法（以数组排序为例）
  6. MySQL5.5.源码安装
  7. HDU-1995-汉诺塔V
  8. 数据结构3——浅谈zkw线段树
  9. JMeter 之Synchronizing Timer运行原理
  10. Notes:一致性哈希算法
  11. C# 内插字符串与字符串复合格式
  12. [wiki] Unix like
  13. bottle.py中的路由搜索优化
  14. Express 体验 路由、模板引擎、中间件
  15. Windows Service 2012 R2 下如何建立ftp服务器
  16. 周强201771010141《面向对象程序设计（java）》第一周学习总结
  17. /usr/bin/ld: cannot find -lncurses是咋回事？
  18. 【Hadoop学习之九】MapReduce案例分析一-天气
  19. 对应web的常用flutter应用
  20. 初学HTML-4

热门文章

  1. 允许ubuntu下mysql远程连接
  2. R语言聚类方法&主要软件包-K-means
  3. C# - DynamicObject with Dynamic
  4. Actions 动作集
  5. iterm2相关配置
  6. iOS开发笔记系列-基础5(分类和协议)
  7. 【原创】测试不同浏览器播放canvas动画的平滑程度
  8. jsp forward 动作标签
  9. Scrapy 对不同的Item进行分开存储
  10. 浅谈Linux的内存管理机制