Sql日期时间格式转换

一、概述

本篇文章转载来着官网在线文档,文章主要介绍SQL
Server数据类型转换相关语法、隐式转换、Date样式等。

 

图片 1语法

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

参数

expression:任何有效的表达式。

data_type:目标数据类型。这包括 xmlbigint
sql_variant。不能使用别名数据类型。有关可用数据类型的详细信息,请参阅数据类型
(Transact-SQL)。

length:指定目标数据类型长度的可选整数。默认值为 30。

style:指定 CONVERT 函数如何转换 expression 的整数表达式。如果样式为
NULL,则返回 NULL。该范围是由 data_type
确定的。有关详细信息,请参阅“备注”部分。

1、数据类型转换:

  • (1)两种转换场合:
  • A:当两个结果集之间的数据进行比较或联和(union)的时候,如果两个结果集的数据类型不同时必须进行转换。
  • B:将Transact-SQL
    得结果集返回给程序中的变量时,须将结果集的类型从SQL
    Server数据类型转换为变量的数据类型。
  • (2)两种转换方式:
  • A:自动进行的隐性转换(对于用户是不可见的)。 如:一个 smallint
    变量和 int 变量比较时,将smallint 变量在比较前被隐性转换成 int
    变量。
  • B:用户提供的显式转换(使用 CAST 或 CONVERT 函数进行显式转换)。

注意

A:目标数据类型不能使用别名数据类型。
nchar、nvarchar、char、varchar、binary 或 varbinary

B:目标数据格式的样式在下列类型间转换时才有效

(1)datetime 或 smalldatetime
数据转换成字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar
数据类型)

(2)已知日期或时间格式的字符数据转换成 datetime 或 smalldatetime
数据,或是字符串格式

(3)用于将 float、real、money 或 smallmoney
数据转换成字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar
数据类型)。

经常会用到,一直没留底,今天找了一段来,以供参考

Date 和 Time 样式

如果 expression 为 date 或 time 数据类型,则 style
可以为下表中显示的值之一。其他值作为 0 进行处理。SQL Server
使用科威特算法来支持阿拉伯样式的日期格式。

不带世纪数位 (yy) (1) 带世纪数位 (yyyy) 标准 输入/输出 (3)

0100 (1, 2)

默认

mon dd yyyy hh:miAM(或 PM)

1

101

美国

mm/dd/yyyy

2

102

ANSI

yy.mm.dd

3

103

英国/法国

dd/mm/yyyy

4

104

德国

dd.mm.yy

5

105

意大利

dd-mm-yy

6

106 (1)

dd mon yy

7

107 (1)

mon dd, yy

8

108

hh:mi:ss

9109 (1, 2)

默认设置 + 毫秒

mon dd yyyy hh:mi:ss:mmmAM(或 PM)

10

110

美国

mm-dd-yy

11

111

日本

yy/mm/dd

12

112

ISO

yymmdd

yyyymmdd

13113 (1, 2)

欧洲默认设置 + 毫秒

dd mon yyyy hh:mi:ss:mmm(24h)

14

114

hh:mi:ss:mmm(24h)

20120 (2)

ODBC 规范

yyyy-mm-dd hh:mi:ss(24h)

21121 (2)

ODBC 规范(带毫秒)

yyyy-mm-dd hh:mi:ss.mmm(24h)

126 (4)

ISO8601

yyyy-mm-ddThh:mi:ss.mmm(无空格)

127(6, 7)

带时区 Z 的 ISO8601。

yyyy-mm-ddThh:mi:ss.mmmZ

(无空格)

130 (1, 2)

回历 (5)

dd mon yyyy hh:mi:ss:mmmAM

131 (2)

回历 (5)

dd/mm/yy hh:mi:ss:mmmAM

1 这些样式值将返回不确定的结果。包括所有
(yy)(不带世纪数位)样式和一部分 (yyyy)(带世纪数位)样式。

2 默认值(style 0100910913
11320120 以及 21121)始终返回世纪数位
(yyyy)。

3 转换为 datetime 时输入;转换为字符数据时输出。

4 为用于 XML 而设计。对于从 datetimesmalldatetime
到字符数据的转换,其输出格式如上一个表所述。

5 回历是有多种变体的日历系统。SQL Server 使用科威特算法。

注意:默认情况下,SQL
Server 基于截止年份 2049
年来解释两位数的年份。换言之,就是将两位数的年份 49 解释为
2049,将两位数的年份 50 解释为
1950。许多客户端应用程序(如基于自动化对象的应用程序)都使用截止年份
2030 年。SQL Server 提供了 two digit year cutoff
配置选项,可通过此选项更改 SQL Server
使用的截止年份,从而对日期进行一致处理。建议您指定四位数年份。

6 仅支持从字符数据转换为 datetime
smalldatetime。仅表示日期或时间成分的字符数据转换为 datetime
smalldatetime 数据类型时,未指定的时间成分设置为
00:00:00.000,未指定的日期成分设置为 1900-01-01。

7使用可选的时间区域指示符 (Z) 更便于将具有时区信息的 XML datetime
值映射到没有时区的 SQL Server datetime 值。Z 是时区 UTC-0
的指示符。其他时区则以 + 或 – 方向的 HH:MM
偏移量来指示。例如:2006-12-12T23:45:12-08:00

smalldatetime
转换为字符数据时,包含秒或毫秒的样式将在这些位置上显示零。使用相应的
charvarchar 数据类型长度从 datetimesmalldatetime
值转换时,可截断不需要的日期部分。

从样式包含时间的字符数据转换为 datetimeoffset
时,将在结果末尾追加时区偏移量。

3、备注(来着MSDN.aspx))

 

float 和 real 样式

如果 expression 为 floatreal,则 style
可以为下表中显示的值之一。其他值作为 0 进行处理。

输出

0(默认值)

最多包含 6 位。根据需要使用科学记数法。

1

始终为 8 位值。始终使用科学记数法。

2

始终为 16 位值。始终使用科学记数法。

126, 128, 129

为了保持向后兼容而包括在内,在以后的版本中可能不推荐使用。

(1)大值数据类型

大值数据类型表现出与小值数据类型相同的隐式和显式转换行为,特别是
varcharnvarcharvarbinary
数据类型。但是,应该考虑以下原则:

  • imagevarbinary(max)
    的转换与反向转换是隐式转换,text
    varchar(max)ntextnvarchar(max)
    之间的转换也是隐式转换。
  • 从大值数据类型(如 varchar(max))到小值数据类型(如
    varchar)的转换是隐式转换,但如果大值相对于指定长度的小值数据类型显得太大,则产生截断。
  • varcharnvarcharvarbinary
    到其相应的大值数据类型的转换都是隐式执行的。
  • sql_variant 数据类型到大值数据类型的转换是显式转换。
  • 大值数据类型不能转换为 sql_variant 数据类型。
  • ##### (2)xml 数据类型

    当您将 xml
    数据类型显式或隐式转换为字符串或二进制数据类型时,xml
    数据类型的内容将根据一组规则进行序列化。

    (3)文本和图像数据类型

    不支持对 textimage 数据类型进行自动数据类型转换。可将
    text 数据显式转换为字符数据,将 image 数据转换为 binary
    varbinary,但最大长度是 8000
    字节。如果试图进行不正确的转换,如将包含字母的字符表达式转换为
    int,则 SQL Server 将返回错误消息。

    ##### (4)输出的排序规则

    如果 CAST 或 CONVERT
    的输出是字符串,并且输入也是字符串,则输出将与输入具有相同的排序规则和排序规则标签。如果输入不是字符串,则输出采用数据库的默认排序规则以及强制默认的排序规则标签。请参阅排序规则优先级
    (Transact-SQL).aspx)。

    若要为输出分配不同的排序规则,请将 COLLATE 子句应用于 CAST 或
    CONVERT 函数的结果表达式。例如:

    SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

    ##### (5)截断结果和舍入结果

    将字符或二进制表达式(charncharnvarcharvarcharbinary

    varbinary)转换为其他数据类型的表达式时,可截断数据,仅显示部分数据,或返回错误(因为结果太短而无法显示)。除了下表显示的转换,其他到
    charvarcharncharnvarcharbinary
    varbinary 的转换都将被截断。

sql server2000中使用convert来取得datetime数据类型样式(全)

money 和 smallmoney 样式

如果 expression 为 moneysmallmoney,则 style
可以为下表中显示的值之一。其他值作为 0 进行处理。

输出

0(默认值)

小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数,例如 4235.98。

1

小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数,例如 3,510.92。

2

小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数,例如 4235.9819。

126

转换为 char(n) 或 varchar(n) 时,等同于样式 2

日期数据格式的处理,两个示例:

xml 样式

如果 data_type 为 xml,则 style
可以为下表中显示的值之一。其他值作为 0 进行处理。

输出

0(默认值)

使用默认的分析行为,即放弃无用的空格,且不允许使用内部 DTD 子集。

注意:

转换为 xml 数据类型时,SQL Server 的无用空格处理方式不同于 XML 1.0。有关详细信息,请参阅生成 XML 实例。

1

保留无用空格。此样式设置将默认的 xml:space 处理方式设置为与指定了 xml:space="preserve" 的行为相同。

2

启用有限的内部 DTD 子集处理。

如果启用,则服务器可使用内部 DTD 子集提供的以下信息来执行非验证分析操作。

应用属性的默认值。

解析并扩展内部实体引用。

检查 DTD 内容模型以实现语法的正确性。

分析器将忽略外部 DTD 子集。此外,不评估 XML 声明来查看 standalone 属性是设置为 yes 还是 no,而是将 XML 实例当成一个独立文档进行分析。

3

保留无用空格,并启用有限的内部 DTD 子集处理。

CONVERT(varchar(16), 时间一, 20) 结果:2007-02-01 
08:02/*时间一般为getdate()函数或数据表里的字段*/