数据库中的日期转换

数据库中的日期转换

针对于目前常用的数据库Access,SqlServer,Oracle,经常有日期型的数据和字符串之间进行转换,整理如下。


  1. Access中的日期转换

    日期型转换为字符串

    Format函数

    SELECT Format(now(), ' yyyy/MM/ddHH:mm:ss.SSS ' ) As dt
    输出结果
    dt
    2007/04/17 18:56:24.2424


    字符串转换为日期

    【yyyy/MM/dd】或者【yyyy-MM-dd】格式的字符串,用DateValue进行转换
    SELECT DateValue( ' 2007/04/16 ' ) as dt1,DateValue( ' 2007-04-14 ' ) As dt2
    输出结果
    dt1dt2
    2007/04/162007/04/14

    【yyyyMMdd】格式的字符串,需要先转换为【yyyy/MM/dd】格式再用DateValue转换
    SELECT DateValue(Mid( ' 20070416 ' , 1 , 4 ) & ' - ' & Mid( ' 20070416 ' , 5 , 2 ) & ' - ' & Mid( ' 20070416 ' , 7 , 2 )) as dt
    输出结果
    dt
    2007/04/16
    2007/04/16
    2007/04/16

  2. SqlServer中的日期转换

    在SqlServer中,与Access Format(),DateValue()函数相对应的是Convert()和Cast()函数,方括号内是可选项,[ style ]的表示形式参照下面的一览表。

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

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

    在下表中,左侧的两列表示将 datetime 或 smalldatetime 数据转换为字符数据的 style 值。将 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)。

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

    -

    0 或 100 (1,2)

    默认设置

    mon dd yyyy hh:miAM(或 PM)

    1

    101

    美国

    mm/dd/yyyy

    2

    102

    ANSI

    yy.mm.dd

    3

    103

    英国/法国

    dd/mm/yy

    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

    -

    9 或 109 (1,2)

    默认设置 + 毫秒

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

    10

    110

    美国

    mm-dd-yy

    11

    111

    日本

    yy/mm/dd

    12

    112

    ISO

    yymmdd

    -

    13 或 113 (1,2)

    欧洲默认设置 + 毫秒

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

    14

    114

    -

    hh:mi:ss:mmm(24h)

    -

    20 或 120 (2)

    ODBC 规范

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

    -

    21 或 121 (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 0 或 100、9 或 109、13 或 113、20 或 120 以及 21 或 121)始终返回世纪数位 (yyyy)。
    3 转换为 datetime 时输入;转换为字符数据时输出。
    4 为用于 XML 而设计。对于从 datetime 或 smalldatetime 到字符数据的转换,其输出格式如上一个表所述。
    5. 回历是有多种变体的日历系统。SQL Server 2005 使用科威特算法。

    日期转换为字符串

    Convert()函数

    Select Convert ( nchar , getdate (), 126 ) as dt

    输出结果:
    2007-04-18T10:18:17.130

    或者用 Cast()函数

    Select Cast ( getdate () as varchar ) as dt

    输出结果:
    04 18 2007 10:23AM

    字符串转换为日期

    Convert()函数

    Select Convert ( datetime , ' 2007/04/18 ' ) as dt

    输出结果:
    2007-04-18 00:00:00.000

    Cast()函数

    Select Cast ( ' 2007/04/18 ' as datetime ) as dt

    输出结果:
    2007-04-18 00:00:00.000

    综合来说,Convert()函数用起来要方便一些。

  3. Oracle中的日期转换

    与之相对应的是To_Char()和To_Date()函数

    日期转换为字符串

    select to_char(sysdate, ' yyyy/MM/ddhh24:mm:ss ' )as dt from dual;

    【hh24】表示时间是24小时制,【hh】表示12小时制
    select to_char(sysdate, ' hh ' )as hour12, to_char(sysdate,'hh24')as hour24


    字符串转换为日期
    Select TO_DATE( ' 2007-04-18 ' , ' yyyy-mm-dd ' ) as dt

    如果想设置日期的语言,需要加上 'NLS_DATE_LANGUAGE = American'参数
    Select TO_DATE( ' 2002-08-26 ' , ' YYYY-mm-dd ' , ' NLS_DATE_LANGUAGE=American ' ) as dt

数据库中的日期转换

针对于目前常用的数据库Access,SqlServer,Oracle,经常有日期型的数据和字符串之间进行转换,整理如下。


  1. Access中的日期转换

    日期型转换为字符串

    Format函数

    SELECT Format(now(), ' yyyy/MM/ddHH:mm:ss.SSS ' ) As dt
    输出结果
    dt
    2007/04/17 18:56:24.2424


    字符串转换为日期

    【yyyy/MM/dd】或者【yyyy-MM-dd】格式的字符串,用DateValue进行转换
    SELECT DateValue( ' 2007/04/16 ' ) as dt1,DateValue( ' 2007-04-14 ' ) As dt2
    输出结果
    dt1dt2
    2007/04/162007/04/14

    【yyyyMMdd】格式的字符串,需要先转换为【yyyy/MM/dd】格式再用DateValue转换
    SELECT DateValue(Mid( ' 20070416 ' , 1 , 4 ) & ' - ' & Mid( ' 20070416 ' , 5 , 2 ) & ' - ' & Mid( ' 20070416 ' , 7 , 2 )) as dt
    输出结果
    dt
    2007/04/16
    2007/04/16
    2007/04/16

  2. SqlServer中的日期转换

    在SqlServer中,与Access Format(),DateValue()函数相对应的是Convert()和Cast()函数,方括号内是可选项,[ style ]的表示形式参照下面的一览表。

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

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

    在下表中,左侧的两列表示将 datetime 或 smalldatetime 数据转换为字符数据的 style 值。将 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)。

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

    -

    0 或 100 (1,2)

    默认设置

    mon dd yyyy hh:miAM(或 PM)

    1

    101

    美国

    mm/dd/yyyy

    2

    102

    ANSI

    yy.mm.dd

    3

    103

    英国/法国

    dd/mm/yy

    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

    -

    9 或 109 (1,2)

    默认设置 + 毫秒

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

    10

    110

    美国

    mm-dd-yy

    11

    111

    日本

    yy/mm/dd

    12

    112

    ISO

    yymmdd

    -

    13 或 113 (1,2)

    欧洲默认设置 + 毫秒

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

    14

    114

    -

    hh:mi:ss:mmm(24h)

    -

    20 或 120 (2)

    ODBC 规范

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

    -

    21 或 121 (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 0 或 100、9 或 109、13 或 113、20 或 120 以及 21 或 121)始终返回世纪数位 (yyyy)。
    3 转换为 datetime 时输入;转换为字符数据时输出。
    4 为用于 XML 而设计。对于从 datetime 或 smalldatetime 到字符数据的转换,其输出格式如上一个表所述。
    5. 回历是有多种变体的日历系统。SQL Server 2005 使用科威特算法。

    日期转换为字符串

    Convert()函数

    Select Convert ( nchar , getdate (), 126 ) as dt

    输出结果:
    2007-04-18T10:18:17.130

    或者用 Cast()函数

    Select Cast ( getdate () as varchar ) as dt

    输出结果:
    04 18 2007 10:23AM

    字符串转换为日期

    Convert()函数

    Select Convert ( datetime , ' 2007/04/18 ' ) as dt

    输出结果:
    2007-04-18 00:00:00.000

    Cast()函数

    Select Cast ( ' 2007/04/18 ' as datetime ) as dt

    输出结果:
    2007-04-18 00:00:00.000

    综合来说,Convert()函数用起来要方便一些。

  3. Oracle中的日期转换

    与之相对应的是To_Char()和To_Date()函数

    日期转换为字符串

    select to_char(sysdate, ' yyyy/MM/ddhh24:mm:ss ' )as dt from dual;

    【hh24】表示时间是24小时制,【hh】表示12小时制
    select to_char(sysdate, ' hh ' )as hour12, to_char(sysdate,'hh24')as hour24


    字符串转换为日期
    Select TO_DATE( ' 2007-04-18 ' , ' yyyy-mm-dd ' ) as dt

    如果想设置日期的语言,需要加上 'NLS_DATE_LANGUAGE = American'参数
    Select TO_DATE( ' 2002-08-26 ' , ' YYYY-mm-dd ' , ' NLS_DATE_LANGUAGE=American ' ) as dt
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值