ISDATE等价于DB2 [英] ISDATE equivalent of DB2
问题描述
实际上,它看起来像是DB2 for AS / 400可能没有有 ISDATE()
函数(在V6R1引用中找不到任何东西 - 或者有趣的是,LUW引用) 。所以你的问题似乎是这个函数不存在。
当然这个问题的根源在于尝试翻译一个无效的日期导致语句停止。鉴于此,如果格式化可能,此声明应给您一个日期,如果不是,则应为null。请注意,如果您混合了美国和欧洲格式/订单,您可能 不可以正确恢复数据(如果您的分隔符不同,我认为默认情况下,你可能会没事的)。
WITH date_format(strDate,format)as(
SELECT strDate,
CASE
WHEN strDate LIKE('____ _ _ _ _ _''THEN'ISO'
WHEN strDate LIKE('__.__.____')THEN'EUR'
WHEN strDate LIKE('__ / __ / ____')THEN'USA'
ELSE NULL END
FROM dataTable
)
SELECT
strDate,
格式,
CASE
WHEN格式IS NOT NULL THEN DATE(strDate)
ELSE NULL
END作为realDate
FROM date_format
这将转换为如下表所示的数据表:
字符串日期
=============
2011-09-22
22.09.2011
09/22/2011
a111x90x00 - 谁知道这是什么...
进入: p>
结果:
strDate格式realDate
============= =============
2011-09-22 ISO 2011-09-22
22.09.2011 EUR 2011-09-22
09/22/2011美国2011-09-22
a111x90x00 - -
这个例子当然是使用默认自动翻译的格式。如果你还有其他的东西,你必须手动翻译它(而不是返回格式,你可以将它们重写成ISO,然后再转换)。
I have a table which contains date in character format . I want to check the format of date . Please let me know how i can do this in DB2. I know there is a function ISDATE but its not working in DB2. I am on AS400 using db2 as date base .. Please help me out
Actually, it looks like DB2 for the AS/400 may not have the ISDATE()
function (I can't find anything in the V6R1 reference - or, interestingly, the LUW reference either). So your problem appears to be that the function does not exist.
The root of the problem, of course, is that attempting to translate an invalid date causes the statement to halt. In light of that, this statement should give you a date if the formatting was possible, and null if it was not. Please note that if you've mixed USA and EUR formats/ordering, you might not be able to correctly recover the data (if your separators are different, which I think they are by default, you'll probably be okay).
WITH date_format(strDate, format) as (
SELECT strDate,
CASE
WHEN strDate LIKE('____-__-__') THEN 'ISO'
WHEN strDate LIKE('__.__.____') THEN 'EUR'
WHEN strDate LIKE('__/__/____') THEN 'USA'
ELSE NULL END
FROM dataTable
)
SELECT
strDate,
format,
CASE
WHEN format IS NOT NULL THEN DATE(strDate)
ELSE NULL
END AS realDate
FROM date_format
This turns a dataTable looking like this:
String Dates
=============
2011-09-22
22.09.2011
09/22/2011
a111x90x00 -- And who knows what this is...
Into this:
Results:
strDate format realDate
============================
2011-09-22 ISO 2011-09-22
22.09.2011 EUR 2011-09-22
09/22/2011 USA 2011-09-22
a111x90x00 - -
This example is of course using the default formats which auto-translate. If you have something else, you'll have to manually translate it (instead of returning the format, you can substring it into ISO then cast it).
这篇关于ISDATE等价于DB2的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!