ISDATE 等效于 DB2 [英] ISDATE equivalent of DB2

问题描述

我有一个包含字符格式日期的表格.我想检查 date 的格式.请让我知道如何在 DB2 中做到这一点.我知道有一个函数 ISDATE 但它在 DB2 中不起作用.我在使用 db2 作为日期库的 AS400 上.请帮帮我

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

推荐答案

实际上，看起来 DB2 for AS/400 可能没有有 ISDATE() 函数(我在 V6R1 参考中找不到任何东西——或者，有趣的是，LUW 参考中也找不到).所以你的问题似乎是该函数不存在.

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.

当然，问题的根源在于尝试翻译无效日期会导致语句停止.鉴于此，如果格式可行，此语句应为您提供日期，否则为 null.请注意，如果您混合了美国和欧元格式/排序，您可能 不能够正确恢复数据(如果您的分隔符不同，我认为他们默认情况下，您可能会没事的).

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...  

进入这个:

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   -        -            

这个例子当然是使用自动翻译的默认格式.如果您还有其他内容，则必须手动翻译它(而不是返回格式，您可以将其子串到 ISO 中，然后进行转换).

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屋！