KeepNotes blog

Stay hungry, Stay Foolish.

0%

SQL server 日期操作

Posted on Edited on In ,
Symbols count in article: 4.2k Reading time ≈ 4 mins.

最近在用SQL处理数据库时，遇到对于时间数据的一些问题，刚好结合网上资料以及一些网上课程（DataCamp数据分析）整理下： #### Working with Dates and Times

SQL Server中有四个日期类型：date，datetime，datetimeoffset以及datetime2(7)

  • date对应的数据格式为：yyyy-mm-dd
  • datetime对应的数据格式为：yyyy-mm-dd hh:mm:ss.fff(精确到1毫秒)
  • datetime2(7)对应的数据格式为：yyyy-mm-dd hh:mm:ss.fffffff（精确到0.1微秒）
  • datetimeoffset(7)相比datetime2(7)加入了时区偏移量部分

几个的区别可参照：DateTime , DateTime2 ,DateTimeOffset 之间的小区别

声明一个datetime2(7)数据格式的变量

declare @SomeTime DATETIME2(7) = SYSUTCDATETIME()

YEAR()，MONTH()以及DAY()函数能分别返回日期对应的年/月/日

select YEAR(@SomeTime) AS TheYear,
    MONTH(@SomeTime) AS TheMonth,
    DAY(@SomeTime) AS TheDay

DATEPART()函数用于返回日期的其中一部分，比如年/月/日/小时/分钟等等

declare @BerlinWallFalls DATETIME2(7) = '1989-11-09 23:49:36.2294852';

select
    DATEPART(year, @BerlinWallFalls) AS TheYear,
    DATEPART(month, @BerlinWallFalls) AS TheMonth,
    DATEPART(day, @BerlinWallFalls) AS TheDay,
    DATEPART(dayofyear, @BerlinWallFalls) AS TheDayOfYear,
    DATEPART(weekday, @BerlinWallFalls) AS TheDayOfWeek,
    DATEPART(week, @BerlinWallFalls) AS TheWeek,
    DATEPART(second, @BerlinWallFalls) AS TheSecond,
    DATEPART(nanosecond, @BerlinWallFalls) AS TheNanosecond

而DateName()用于返回日期的指定日期部分的字符串，比如11月返回的不是11而是November

DATEADD()用于增减时间间隔来获得一个新日期，而DATEDIFF()则是计算两个日期的间隔，两者的间隔可以是年/月/周/日/小时等等；还能有效的处理闰年份

DECLARE @LeapDay DATETIME2(7) = '2012-02-29 18:00:00';

-- Fill in the appropriate function and date types
select DATEADD(day, -1, DATEADD(year, 4, @PostLeapDay)) AS NextLeapDay

通过上述两个函数的搭配，可以组成对于日期的round操作，比如找相对日期最近的年份

select DATEADD(YEAR, DATEDIFF(YEAR, 0, '1914-08-16'), 0) as Nearest_year

Converting to Dates and Times

最近刚好遇到一个日期和字符串之间的转化问题

如果比较 char 和 datetime 表达式、smallint 和 int 表达式或不同长度的 char 表达式，则 SQL Server 自动对这些表达式进行转换。这种转换称为隐式转换

常用的有CONVERT()函数来做转化，但也可以用CAST()函数，但在时间转换时CONVERT函数具有style格式，具体style code只能用到时再去查查了。。。

select
    CAST('2016-11-03 00:30:29.245' AS NVARCHAR(30)) AS StringForm
    CONVERT(NVARCHAR(30), '2016-11-03 00:30:29.245', 0) AS DefaultForm,
    -- This is a two-digit year code
    CONVERT(NVARCHAR(30), '2016-11-03 00:30:29.245', 1) AS US_mdy,
    -- All of these are four-digit year codes
    CONVERT(NVARCHAR(30), '2016-11-03 00:30:29.245', 104) AS DE_dmyyyy,
    CONVERT(NVARCHAR(30), '2016-11-03 00:30:29.245', 111) AS JP_yyyymd,
    CONVERT(NVARCHAR(30), '2016-11-03 00:30:29.245', 112) AS ISO_yyyymmdd,
    CONVERT(NVARCHAR(30), '2016-11-03 00:30:29.245', 126) AS ISO8601

FORMAT()函数可以设定日期的类型，其第三个参数可以设定en-US或者zh-cn等类型，如设置为短日期d或者长日期D，当然还有展示年yyyy/月MM/日dd等方法：

SELECT
    -- Fill in the function call and format parameter
    FORMAT('2008-12-03 19:45:00.033', 'd', 'en-US') AS US_d,
    FORMAT('2008-12-03 19:45:00.033', 'D', 'zh-cn') AS CN_d
    FORMAT('2008-12-03 19:45:00.033', 'yyyyMMdd') AS Year

DATEFROMPARTS函数可以返回映射到指定年/月/日的date值

select DATEFROMPARTS(2019, 09, 17) AS Result

DATETIME2FROMPARTS函数可以对指定日期和时间参数返回 datetime2格式的日期值，而DATETIMEFROMPARTS函数可以对指定日期和时间参数返回datetime格式的日期值

DATETIMEFROMPARTS参数分别是DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )；而DATETIME2FROMPARTS参数分别是DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )，其中fractions表示秒的小数形式值的整数表达式，而fractions表示返回的datetime2值的精度

select DATETIMEFROMPARTS(2019, 12, 12, 10, 00, 00, 500) AS Result
select DATETIME2FROMPARTS (2019, 12, 12, 10, 00, 00, 5, 1 )

而DATETIMEOFFSETFROMPARTS函数则可以返回指定日期和时间参数的datetimeoffset格式的日期值，其参数则是DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

select DATETIMEOFFSETFROMPARTS (2019, 12, 12, 10, 22, 20, 5, 12, 30, 1) AS Result

SYSDATETIME函数返回的是计算机datetime2(7)格式的日期值，而SYSUTCDATETIME函数返回的是计算datetime2格式的日期值，如以官方的例子：

SELECT SYSDATETIME() AS [SYSDATETIME()]  
    ,SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()]  
    ,SYSUTCDATETIME() AS [SYSUTCDATETIME()]  
    ,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]  
    ,GETDATE() AS [GETDATE()]  
    ,GETUTCDATE() AS [GETUTCDATE()]

--结果：
SYSDATETIME()      2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME()   2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP  2007-04-30 13:10:02.047
GETDATE()          2007-04-30 13:10:02.047
GETUTCDATE()       2007-04-30 20:10:02.047

在SQL Server中，如果CONVERT函数转化类型失败，则会报错；这时可以用TRY_CONVERT，还有类似的TRY_CAST和TRY_PARSE函数，其如果转化失败则返回NULL

select
    TRY_CAST('4/13/2019' AS DATE) AS date1,
    TRY_CONVERT(DATE, '4/13/2019') AS date2,
    TRY_PARSE('4/13/2019' AS DATE USING 'en-us') AS date3

参考资料：
日期和时间数据类型及函数 (Transact-SQL)
Time Series Analysis in SQL Server

本文出自于http://www.bioinfo-scrounger.com转载请注明出处

Related Posts