前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >[数据库基础]——快速浏览日期时间转换

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

作者头像
数据分析
发布于 2018-03-01 16:50:35
1K0
发布于 2018-03-01 16:50:35
举报
文章被收录于专栏：数据分析数据分析

阅读导航

数据库日期和时间类型

相互转换

    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

代码语言：js
复制
   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

代码语言：js
复制
   1:  -- date -> time
   2:  DECLARE @date date= '12-10-25';
   3:  DECLARE @time time= @date;
   4:   
   5:  SELECT @date AS '@date', @time AS '@time';

Result：

Operand type clash: time is incompatible with date

time ⇌ smalldatetime

代码语言：js
复制
   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';

Result：

@time               @smalldatetime 12:15:04.1234    1900-01-01 12:15:00

代码语言：js
复制
   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';

Result：

@smalldatetime        @time 1995-12-13 12:43:00    12:43:00.0000

time ⇌ datetime

代码语言：js
复制
   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';

Result：

@time                      @datetime 12:10:05.1234            1900-01-01 12:10:05.123

代码语言：js
复制
   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';

Result：

@datetime                       @time 2005-11-12 12:10:05.123    12:10:05.1230

代码语言：js
复制
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';

Result：

@time               @datetime2 12:15:04.1234    1900-01-01 12:15:04.123

代码语言：js
复制
   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)';

Result：

@datetime2                       @time(3) 2025-10-12 12:32:10.1234    12:32:10.123

time ⇌ datetimeoffset

代码语言：js
复制
   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';

Result：

@time              @datetimeoffset 12:15:04.1234    1900-01-01 12:15:04.123 +00:00

代码语言：js
复制
   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';

Result：

@datetimeoffset                           @time 2025-10-12 12:32:10.1234 +01:00    12:32:10.123

date ⇌ smalldatetime

代码语言：js
复制
   1:  -- date -> smalldatetime
   2:  DECLARE @date date= '1912-10-25';
   3:  DECLARE @smalldatetime smalldatetime = @date;
   4:   
   5:  SELECT @date AS '@date', @smalldatetime AS '@smalldatetime';

Result：

@date          @smalldatetime 1912-10-25    1912-10-25 00:00:00

代码语言：js
复制
   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';

Result：

@smalldatetime        @date 1955-12-13 12:43:00    1955-12-13

date ⇌ datetime

代码语言：js
复制
   1:  -- date -> datetime
   2:  DECLARE @date date= '12-10-25';
   3:  DECLARE @datetime datetime= @date;
   4:   
   5:  SELECT @date AS '@date', @datetime AS '@datetime';

Result：

@date           @datetime 2025-10-12    2025-10-12 00:00:00.000

代码语言：js
复制
   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';

Result：

@datetime                        @date 2005-11-12 12:43:10.000    2005-11-12

date ⇌ datetime2

代码语言：js
复制
   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)';

Result：

@date           @datetime2(3) 2030-10-25    2030-10-25 00:00:00.000

代码语言：js
复制
   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';

Result：

@datetime2                        @date 2025-10-12 12:32:10.1234    2025-10-12

date ⇌ datetimeoffset

代码语言：js
复制
   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';

Result：

@date           @datetimeoffset 1912-10-25    1912-10-25 00:00:00.000 +00:00

代码语言：js
复制
   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';

Result：

@datetimeoffset                             @date 2025-10-12 12:32:10.0000 +01:00    2025-10-12

smalldatetime ⇌ datetime

代码语言：js
复制
   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';

Result：

@smalldatetime          @datetime 2040-10-12 12:43:00    2040-10-12 12:43:00.000

代码语言：js
复制
   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';

Result：

datetime                           @smalldatetime 2005-11-12 12:43:10.123    2005-11-12 12:43:00

smalldatetime ⇌ datetime2

代码语言：js
复制
   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)';

Result：

@smalldatetime          @datetime2(4) 2011-06-12 12:43:00    2011-06-12 12:43:00.0000

代码语言：js
复制
   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';

Result：

@datetime2                             @smalldatetime 2025-10-12 12:32:10.1234567    2025-10-12 12:32:00

smalldatetime ⇌ datetimeoffset

代码语言：js
复制
   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)';

Result：

@smalldatetime          @datetimeoffset(4) 2013-10-12 12:43:00    2013-10-12 12:43:00.0000 +00:00

代码语言：js
复制
   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';

Result：

@datetimeoffset                            @smalldatetime 1912-10-25 12:24:32.000 +10:00    1912-10-25 12:25:00

datetime ⇌ datetime2

代码语言：js
复制
   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'

Result：

@datetime                        @datetime2 2014-10-10 12:45:37.123    2014-10-10 12:45:37.1230

代码语言：js
复制
   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';

Result：

@datetime2                        @datetime 1968-10-23 12:45:37.1237    1968-10-23 12:45:37.123

datetime ⇌ datetimeoffset

代码语言：js
复制
   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';

Result：

@datetime                        @datetimeoffset 2025-10-12 12:32:10.123    2025-10-12 12:32:10.1230000 +00:00

代码语言：js
复制
   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';

Result：

@datetimeoffset                             @datetime 2025-10-12 12:32:10.1234 +01:00    2025-10-12 12:32:10.123

datetime2 ⇌ datetimeoffset

代码语言：js
复制
   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)';

Result：

@datetime2                      @datetimeoffset(2) 2025-10-12 12:32:10.123    2025-10-12 12:32:10.12 +00:00

代码语言：js
复制
   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';

Result：

@datetimeoffset                             @datetime2 1912-10-25 12:24:32.1234 +10:00    1912-10-25 12:24:32.123

本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2013-07-09 ，如有侵权请联系 cloudcommunity@tencent.com 删除
数据库
sql

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

数据库
sql
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
产品介绍
精选特惠 用云无忧
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论