代码之家 › 专栏 › 技术社区 › Seibar

SQL Server日期时间转换失败

datetime sql-server

Seibar · 技术社区 · 17 年前

我有一张100多万张唱片的大桌子。不幸的是,创建该表的人决定将日期放在 varchar(50) 字段。

我需要做一个简单的日期比较-

datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31

但它在 convert() :

Conversion failed when converting datetime from character string.

显然,在这个领域有一些它不喜欢的东西,而且由于有这么多的记录,我不能仅仅通过看它就知道。如何正确地清理整个日期字段,使其不会在 转换() ?以下是我现在拥有的:

select count(*)
from MyTable
where
    isdate(lastUpdate) > 0
    and datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31

@SQLMenace

在这种情况下,我不关心性能。这将是一次查询。将表更改为日期时间字段不是一个选项。

@Jon Limjap

我试过加入第三个论点,但没有什么不同。

@ SQLMenace

问题很可能是数据的存储方式,只有两种安全格式:iso-yyyy mm dd;iso 8601-yyyy-mm-dd-thh:mm:ss:mmm(无空格)

不会 isdate() 检查处理好了吗?

我不需要100%的准确度。我只想得到过去30天的大部分记录。

@ SQLMenace

select isdate('20080131') -- returns 1
select isdate('01312008') -- returns 0

@Brian Schkerke

将case和isdate放在convert()函数中。

谢谢!就是这样。

10 回复 | 直到 7 年前

Ansgar Wiechers 7 年前

放置 CASE 和 ISDATE 里面 CONVERT() 功能。

SELECT COUNT(*) FROM MyTable
WHERE
    DATEDIFF(dd, CONVERT(DATETIME, CASE IsDate(lastUpdate)
        WHEN 1 THEN lastUpdate
        ELSE '12-30-1899'
    END), GetDate()) < 31

替换 '12-30-1899' 默认日期。

SQLMenace 17 年前

如何编写一个指针来循环遍历内容,尝试对每个条目进行强制转换?出现错误时,输出问题记录的主键或其他标识详细信息。我想不出一个基于集合的方法来实现这一点。

不是完全基于setbased的,但是如果100万行中只有3行是坏的,那会节省你很多时间。

select * into BadDates
from Yourtable
where isdate(lastUpdate) = 0

select * into GoodDates
from Yourtable
where isdate(lastUpdate) = 1

那就看看坏日期表,把它修好。

Valery Viktorovsky 10 年前

isdate()将处理那些没有正确格式化的行,如果确实是先执行的话。但是,如果您查看执行计划,您可能会发现首先应用了datediff谓词,这也是造成您痛苦的原因。

如果您使用的是SQL Server Management Studio,请点击 CTRL + L 查看特定查询的估计执行计划。

记住,SQL不是一种过程语言,短路逻辑可能有效,但前提是您在如何应用它时要小心。

Jon Limjap 17 年前

在convert调用中,需要指定第三个样式参数,例如,存储为varchar的日期时间的格式,如本文档中指定的: CAST and CONVERT (T-SQL)

Ian Nelson 17 年前

如何编写一个指针来循环遍历内容,尝试对每个条目进行强制转换?

出现错误时,输出问题记录的主键或其他标识详细信息。

我想不出一个基于集合的方法来实现这一点。

编辑 -啊,是的,我忘记了isdate()。绝对比使用光标更好的方法。+1到sqlmenace。

Ian Nelson 17 年前

打印记录。把硬拷贝给决定使用varchar(50)的白痴,让他们找到问题记录。

下一次,他们可能只是看到选择适当数据类型的要点。

SQLMenace 17 年前

问题很可能是数据的存储方式,只有两种安全格式

ISYYYYMFDD

ISO 8601 yyyy-mm-dd-thh:mm:ss:mmm(无空格)

不管你的语言是什么,这些都会起作用。

您可能需要执行一个set-dateformat-ymd(或存储数据的任何形式)以使其正常工作。

SQLMenace 17 年前

isdate()检查不会解决这个问题吗?

运行这个来看看会发生什么

select isdate('20080131')
select isdate('01312008')

chris raethke 17 年前

我敢肯定,由于任何遗留的系统要求,更改表/列可能不是一个选项,但是您是否考虑过创建一个内置了日期转换逻辑的视图,如果您使用的是最新版本的SQL,那么您甚至可以使用索引视图?

Gumbo 14 年前

我建议清理混乱,将列改为日期时间,因为这样做

WHERE datediff(dd, convert(datetime, lastUpdate), getDate()) < 31

无法使用索引,它将比使用datetime colum、n和did时慢很多倍。

where lastUpdate > getDate() -31

当然,你还需要考虑时间和秒数