SQL Server Date

Subtract one day from datetime

DATEADD(day, -1, GETDATE())
DATEADD(hour, 1, GETDATE())

DATEDIFF

DATEDIFF ( [day|hour|minute|second|millisecond] , startdate , enddate )

SELECT DATEDIFF(DAY,  '2012-12-12 12:00:00', '2012-12-13 16:00:00') -- return 1

DECLARE @Start datetime
DECLARE @End datetime
SET @Start = GETDATE()
SET @End = GETDATE()
SELECT DATEDIFF(millisecond, @Start, @End)

Convert String to DateTime

CONVERT(datetime, '2012/12/12 08:30:00')

SELECT CONVERT(varchar, GETDATE(), 1) 

SELECT CONVERT(varchar, CONVERT(datetime, '2012-12-01'), 101) -- 12/01/2012

DATEPART

SELECT DATEPART(YEAR, GETDATE())
SELECT DATEPART(dd, GETDATE())
SELECT DATEPART(ms, GETDATE())

Query time zone

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 
'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone

Query time and time zone offset

SELECT SYSDATETIMEOFFSET() 

Convert DateTime to Date

SELECT CONVERT(date, getdate())

Query UTC time

SELECT GETUTCDATE()  -- 2019-04-04 05:42:45.170

Query more precision date

SELECT SYSDATETIME(), SYSUTCDATETIME() -- Returns a datetime2(7) value, e.g.: 2019-04-04 00:43:41.7743182

SQLServer

这篇文章: 有用 无用
创建 2018-09-29 01:35:37 / 更新 2019-04-06 23:55:33