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

问题描述

我有一个表格，其中包含字符格式的日期。我想检查日期的格式。请让我知道如何在DB2中执行此操作。我知道有一个函数ISDATE，但它在DB2中不起作用。我在AS400上使用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屋！