SQL格式转换问题

Question

有如下语句：

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对该语句的影响与可能的错误原因？

Answer 1

You have to ensure values in attachment column compatible with datetime type.

Answer 2

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

Answer 3

表中的数据不规范导致的， 07  6 2015  4:10PM 这个不是sql server支持的日期格式

Answer 4

i don't get the error when executing 

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

Answer 5

可是如果不加top或者order by关键字是可以转换，不报错的

Answer 6

可是如果不加top或者order by关键字是可以转换，不报错的

不加 top 或者 orer by ， 条件判断的先后顺序就不一样一样了，这样需要 cast 的数据，也不一定一样了， 你只保留 cast 的那个条件，如果不加 top 或者 order by 也不报错，那才能证明不是数据的问题

Answer 7

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.

Answer 8

表中的数据不规范导致的， 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)

Answer 9

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

mon dd yyyy hh:miAM（或 PM）

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

Answer 10

查了一下，邹总很细致

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

---

Love SQL

Answer 11

这转换错误应该在写入解决问题

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

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

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

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

---

Roy Wu(吳熹Blog)(微博)