none
SQL格式转换问题 RRS feed

  • 问题

  • 有如下语句:

    select top 10 * from dbo.View_log_in_del viewlogind0_ where 1=1
     and viewlogind0_.ATTACHMENT<>'-' 
     and cast(viewlogind0_.ATTACHMENT as DATETIME)>='2015-08-03 16:00:00'
     order by viewlogind0_.ORDNO asc

    其中attachment字段默认值为 -

    转换前的格式为 07  6 2015  4:10PM

    当执行语句时会报错:从 varchar 数据类型到 datetime 数据类型的转换产生一个超出范围的值。

    当不执行order by子语句时,不报错

    当不使用top 关键字时,也不报错

    请教order by或者top对该语句的影响与可能的错误原因?


    • 已编辑 BQSs 2015年8月27日 1:17
    2015年8月26日 9:03

答案

  • 研究了一下日期和字符串的转换, 默认的转换使用的格式是:

    mon dd yyyy hh:miAM(或 PM)

    问题就在 mon 这里, 07 6 2015 4:10PM 这个日期,mon 的位置是 07, 如果当前会话 language 对于月份的定义不是数字的话,转换就会失败,比如是 english 的话,月份是用英文月份(全称或简写)表示的,所以在 english 下转换不成功的

    2015年8月28日 1:42

全部回复

  • You have to ensure values in attachment column compatible with datetime type.
    2015年8月26日 17:10
  • Try followings:

    select cast('07 6 2015 4:10PM' as datetime)

    Msg 241, Level 16, State 1, Line 23

    Conversion failed when converting date and/or time from character string.

    select cast('07-6-2015 4:10PM' as datetime)

    2015-07-06 16:10:00.000
    2015年8月26日 17:15
  • 表中的数据不规范导致的, 07  6 2015  4:10PM 这个不是sql server支持的日期格式
    2015年8月27日 1:19
  • i don't get the error when executing 

    select cast('07 6 2015 4:10PM' as datetime) in SQL Server 2012

    2015年8月27日 2:18
  • 可是如果不加top或者order by关键字是可以转换,不报错的
    2015年8月27日 2:19
  • 可是如果不加top或者order by关键字是可以转换,不报错的
    不加 top 或者 orer by , 条件判断的先后顺序就不一样一样了,这样需要 cast 的数据,也不一定一样了, 你只保留 cast 的那个条件,如果不加 top 或者 order by 也不报错,那才能证明不是数据的问题
    2015年8月27日 6:35
  • i don't get the error when executing 

    select cast('07 6 2015 4:10PM' as datetime) in SQL Server 2012


    Just tried it on sql2012 and sql2014, got convertion error in both.
    2015年8月27日 13:23
  • 表中的数据不规范导致的, 07  6 2015  4:10PM 这个不是sql server支持的日期格式

    跟当前的会话的 language 有关系

    -- 这个没有问题
    set language 简体中文
    select cast('07 6 2015 4:10PM' as datetime), cast(' 07  6 2015  4:10PM' as datetime)
    go
    -- 这个有问题
    set language english
    select cast('07 6 2015 4:10PM' as datetime), cast(' 07  6 2015  4:10PM' as datetime)
    

    2015年8月28日 1:29
  • 研究了一下日期和字符串的转换, 默认的转换使用的格式是:

    mon dd yyyy hh:miAM(或 PM)

    问题就在 mon 这里, 07 6 2015 4:10PM 这个日期,mon 的位置是 07, 如果当前会话 language 对于月份的定义不是数字的话,转换就会失败,比如是 english 的话,月份是用英文月份(全称或简写)表示的,所以在 english 下转换不成功的

    2015年8月28日 1:42
  • 查了一下,邹总很细致

    SELECT * FROM [sys].[syslanguages] WHERE [langid] IN(0,30)


    Love SQL

    2015年8月28日 3:03
  • 这转换错误应该在写入解决问题

    在写入时处理类型转换,而不时把日期格式按字符串写入

    把数据库栏位定义类型:datetime

    存储为8个字节,比字符串有效率

    当类型为Datetime时,你显示或转换或显示时可用Convert指定日期格式




    Roy Wu(吳熹Blog)(微博)

    2015年8月28日 7:21
    版主