[数据库基础]——快速浏览日期时间转换

阅读导航

数据库日期和时间类型

相互转换

    time ⇌ date

    time ⇌ smalldatetime

    time ⇌ datetime

    time ⇌ datetime2

    time ⇌ datetimeoffset

    date ⇌ smalldatetime

    date ⇌ datetime

    date ⇌ datetime2

    date ⇌ datetimeoffset

    smalldatetime ⇌ datetime

    smalldatetime ⇌ datetime2

    smalldatetime ⇌ datetimeoffset

    datetime ⇌ datetime2

    datetime ⇌ datetimeoffset

    datetime2 ⇌ datetimeoffset

数据库日期和时间类型：

类型	格式	范围
time	hh:mm:ss[.nnnnnnn]	00:00:00.0000000 ~ 23:59:59.9999999
date	YYYY-MM-DD	0001-01-01 ~ 9999-12-31
smalldatetime	YYYY-MM-DD hh:mm:ss	1900-01-01 ~ 079-06-06
datetime	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 ~ 99-12-31
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000 ~ 99-12-31 23:59:59.9999999
datetimeoffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm	0001-01-01 00:00:00.0000000 ~9999-12-31 23:59:59.9999999 (in UTC)

相互转换（SET DATEFORMAT dmy;）

time ⇌ date

1:  -- time -> date      

2:  DECLARE @time time(4) = '12:15:04.1234';      

3:  DECLARE @date date= @time;      

4:         

5:  SELECT @time AS '@time', @date AS '@date';      

Result：

Operand type clash: time is incompatible with date

1:  -- date -> time      

2:  DECLARE @date date= '12-10-25';      

3:  DECLARE @time time= @date;      

4:         

5:  SELECT @date AS '@date', @time AS '@time';      

time ⇌ smalldatetime

1:  -- time -> smalldatetime      

2:  DECLARE @time time(4) = '12:15:04.1234';      

3:  DECLARE @smalldatetime smalldatetime= @time;      

4:         

5:  SELECT @time AS '@time', @smalldatetime AS '@smalldatetime';      

@time               @smalldatetime

12:15:04.1234    1900-01-01 12:15:00

1:  -- samlldatetime –> time      

2:  DECLARE @smalldatetime smalldatetime = '13-12-1995 12:43:10';      

3:  DECLARE @time time(4) = @smalldatetime;      

4:         

5:  SELECT @smalldatetime AS '@smalldatetime', @time AS 'time';      

@smalldatetime        @time

1995-12-13 12:43:00    12:43:00.0000

time ⇌ datetime

1:  -- time -> datetime      

2:  DECLARE @time time(4) = '12:10:05.1234';      

3:  DECLARE @datetime datetime = @time;      

4:         

5:  SELECT @time AS '@datetime', @datetime AS '@time';      

@time                      @datetime

12:10:05.1234            1900-01-01 12:10:05.123

1:  -- datetime -> time      

2:  DECLARE @datetime datetime = '12-11-05 12:10:05.123';      

3:  DECLARE @time time(4) = @datetime;      

4:         

5:  SELECT @datetime AS '@datetime', @time AS '@time';      

@datetime                       @time

2005-11-12 12:10:05.123    12:10:05.1230

time ⇌ datetime2

1:  -- time -> datetime2      

2:  DECLARE @time time(4) = '12:15:04.1234';      

3:  DECLARE @datetime2 datetime2(3) = @time;      

4:         

5:  SELECT @datetime2 AS '@datetime2', @time AS '@time';      

@time               @datetime2

12:15:04.1234    1900-01-01 12:15:04.123

1:  -- datetime2 -> time      

2:  DECLARE @datetime2 datetime2(4) = '12-10-25 12:32:10.1234';      

3:  DECLARE @time time(3) = @datetime2;      

4:         

5:  SELECT @datetime2 AS '@datetime2', @time AS '@time(3)';      

@datetime2                       @time(3)

2025-10-12 12:32:10.1234    12:32:10.123

time ⇌ datetimeoffset

1:  -- time -> datetimeoffset      

2:  DECLARE @time time(4) = '12:15:04.1234';      

3:  DECLARE @datetimeoffset datetimeoffset(3) = @time;      

4:         

5:  SELECT @time AS '@time', @datetimeoffset AS '@datetimeoffset';      

@time              @datetimeoffset

12:15:04.1234    1900-01-01 12:15:04.123 +00:00

1:  -- datetimeoffset -> time      

2:  DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10.1234 +01:0';      

3:  DECLARE @time time(3) = @datetimeoffset;      

4:         

5:  SELECT @datetimeoffset AS '@datetimeoffset ', @time AS '@time';      

@datetimeoffset                           @time

2025-10-12 12:32:10.1234 +01:00    12:32:10.123

date ⇌ smalldatetime

1:  -- date -> smalldatetime      

2:  DECLARE @date date= '1912-10-25';      

3:  DECLARE @smalldatetime smalldatetime = @date;      

4:         

5:  SELECT @date AS '@date', @smalldatetime AS '@smalldatetime';      

@date          @smalldatetime

1912-10-25    1912-10-25 00:00:00

1:  -- smalldatetime -> date      

2:  DECLARE @smalldatetime smalldatetime = '13-12-1955 12:43:10';      

3:  DECLARE @date date = @smalldatetime      

4:         

5:  SELECT @smalldatetime AS '@smalldatetime', @date AS '@date';      

@smalldatetime        @date

1955-12-13 12:43:00    1955-12-13

date ⇌ datetime

1:  -- date -> datetime      

2:  DECLARE @date date= '12-10-25';      

3:  DECLARE @datetime datetime= @date;      

4:         

5:  SELECT @date AS '@date', @datetime AS '@datetime';      

@date           @datetime

2025-10-12    2025-10-12 00:00:00.000

1:  -- datetime -> date      

2:  DECLARE @datetime datetime = '12-11-05 12:43:10';      

3:  DECLARE @date date = @datetime;      

4:         

5:  SELECT @datetime AS '@datetime', @date AS '@date';      

@datetime                        @date

2005-11-12 12:43:10.000    2005-11-12

date ⇌ datetime2

1:  -- date -> datetime2      

2:  DECLARE @date date = '25-10-2030'      

3:  DECLARE @datetime2 datetime2(3) = @date;      

4:         

5:  SELECT @date AS '@date', @datetime2 AS '@datetime2(3)';      

@date           @datetime2(3)

2030-10-25    2030-10-25 00:00:00.000

1:  -- datetime2 -> date      

2:  DECLARE @datetime2 datetime2(4) = '12-10-25 12:32:10.1234';      

3:  DECLARE @date date = @datetime2;      

4:         

5:  SELECT @datetime2 AS '@datetime2', @date AS '@date';      

@datetime2                        @date

2025-10-12 12:32:10.1234    2025-10-12

date ⇌ datetimeoffset

1:  -- date -> datetimeoffset      

2:  DECLARE @date date = '1912-10-25';      

3:  DECLARE @datetimeoffset datetimeoffset(3) = @date;      

4:         

5:  SELECT @date AS '@date', @datetimeoffset AS '@datetimeoffset';      

@date           @datetimeoffset

1912-10-25    1912-10-25 00:00:00.000 +00:00

1:  -- datetimeoffset -> date      

2:  DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:0';      

3:  DECLARE @date date= @datetimeoffset;      

4:         

5:  SELECT @datetimeoffset AS '@datetimeoffset ', @date AS '@date';      

@datetimeoffset                             @date

2025-10-12 12:32:10.0000 +01:00    2025-10-12

smalldatetime ⇌ datetime

1:  -- smalldatetime -> datetime      

2:  DECLARE @smalldatetime smalldatetime = '12-10-2040 12:43:10';      

3:  DECLARE @datetime datetime = @smalldatetime;      

4:         

5:  SELECT @smalldatetime AS '@smalldatetime', @datetime AS '@datetime';      

@smalldatetime          @datetime

2040-10-12 12:43:00    2040-10-12 12:43:00.000

1:  -- datetime -> smalldatetime      

2:  DECLARE @datetime datetime = '12-11-05 12:43:10.123';      

3:  DECLARE @smalldatetime smalldatetime = @datetime;      

4:         

5:  SELECT @datetime AS 'datetime', @smalldatetime AS '@smalldatetime';      

datetime                           @smalldatetime

2005-11-12 12:43:10.123    2005-11-12 12:43:00

smalldatetime ⇌ datetime2

1:  -- smalldatetime -> datetime2      

2:  DECLARE @smalldatetime smalldatetime = '12-06-2011 12:43:10';      

3:  DECLARE @datetime2 datetime2(4) = @smalldatetime;      

4:         

5:  SELECT @smalldatetime AS '@smalldatetime', @datetime2 AS '@datetime2(4)';      

@smalldatetime          @datetime2(4)

2011-06-12 12:43:00    2011-06-12 12:43:00.0000

1:  -- datetime2 -> smalldatetime      

2:  DECLARE @datetime2 datetime2 = '12-10-25 12:32:10.1234567';      

3:  DECLARE @smalldatetime smalldatetime = @datetime2;      

4:         

5:  SELECT @datetime2 AS '@datetime2', @smalldatetime AS '@smalldatetime';      

@datetime2                             @smalldatetime

2025-10-12 12:32:10.1234567    2025-10-12 12:32:00

smalldatetime ⇌ datetimeoffset

1:  -- samlldatetime -> datetimeoffset      

2:  DECLARE @smalldatetime smalldatetime = '12-10-2013 12:43:10';      

3:  DECLARE @datetimeoffset datetimeoffset(4) = @smalldatetime;      

4:         

5:  SELECT @smalldatetime AS '@smalldatetime', @datetimeoffset AS '@datetimeoffset(4)';      

@smalldatetime          @datetimeoffset(4)

2013-10-12 12:43:00    2013-10-12 12:43:00.0000 +00:00

1:  -- datetimeoffset -> smalldatetime      

2:  DECLARE @datetimeoffset datetimeoffset(3) = '1912-10-25 12:24:32 +10:0';      

3:  DECLARE @smalldatetime smalldatetime = @datetimeoffset;      

4:         

5:  SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime';      

@datetimeoffset                            @smalldatetime

1912-10-25 12:24:32.000 +10:00    1912-10-25 12:25:00

datetime ⇌ datetime2

1:  -- datetimes -> datetime2      

2:  DECLARE @datetime datetime = '10-10-2014 12:45:37.123';      

3:  DECLARE @datetime2 datetime2(4) = @datetime;      

4:         

5:  SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2';      

@datetime                        @datetime2

2014-10-10 12:45:37.123    2014-10-10 12:45:37.1230

1:  -- datetimes2 -> datetime      

2:  DECLARE @datetime2 datetime2(4) = '1968-10-23 12:45:37.1237';      

3:  DECLARE @datetime datetime = @datetime2;      

4:         

5:  SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime';      

@datetime2                        @datetime

1968-10-23 12:45:37.1237    1968-10-23 12:45:37.123

datetime ⇌ datetimeoffset

1:  -- datetime -> datetimeoffset      

2:  DECLARE @datetime datetime = '12-10-25 12:32:10.123';      

3:  DECLARE @datetimeoffset datetimeoffset = @datetime;      

4:         

5:  SELECT @datetime AS '@datetime ', @datetimeoffset AS '@datetimeoffset';      

@datetime                        @datetimeoffset

2025-10-12 12:32:10.123    2025-10-12 12:32:10.1230000 +00:00

1:  -- datetimeoffset -> datetime      

2:  DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10.1234 +01:0';      

3:  DECLARE @datetime datetime = @datetimeoffset;      

4:         

5:  SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS '@datetime';      

@datetimeoffset                             @datetime

2025-10-12 12:32:10.1234 +01:00    2025-10-12 12:32:10.123

datetime2 ⇌ datetimeoffset

1:  -- datetime2 -> datetimeoffset      

2:  DECLARE @datetime2 datetime2(3) = '12-10-25 12:32:10.1234567';      

3:  DECLARE @datetimeoffset datetimeoffset(2) = @datetime2;      

4:         

5:  SELECT @datetime2 AS '@datetime2', @datetimeoffset AS '@datetimeoffset(2)';      

@datetime2                      @datetimeoffset(2)

2025-10-12 12:32:10.123    2025-10-12 12:32:10.12 +00:00

1:  -- datetimeoffset -> datetime2      

2:  DECLARE @datetimeoffset datetimeoffset(4) = '1912-10-25 12:24:32.1234 +10:0';      

3:  DECLARE @datetime2 datetime2(3)= @datetimeoffset;      

4:         

5:  SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';      

@datetimeoffset                             @datetime2

1912-10-25 12:24:32.1234 +10:00    1912-10-25 12:24:32.123