How do I cast a decimal field to a date field in D

问题:

I have a DECIMAL field which contains a date in the AS400 format: 1100614

I have tried:

cast (MYDATE as DATE)

but I can't cast a DECIMAL to DATE, whereas

DATE(MYDATE)

returns null.

How do I cast this field in a DATE field?

回答1:

Assuming it is CYYMMDD like I asked in my comment above, here are a couple options:

1. Convert CYYMMDD format to datetime - SQL
2. convert cyymmdd (AS400) format to datetime format

It can be done, but it isn't easy. If you need to do this often, it may be worth your time writing your own function to do this.

回答2:

This will work if you're on IBM i release 6.1 or newer, but I have a hunch you're on an old version of the operating system. I stole the adding trick from this forum post.

create table dec1 (f1 decimal(7,0))
insert into dec1 values 1100614

select date(timestamp_format(char(f1+19000000), 'YYYYMMDD')) from dec1

Results:

DATE                           
06/14/10                       
********  End of data  ********

Here's the reference to the supported date string formats in DB2 for i SQL.

回答3:

A simpler (and more suitable if you want your own function) way to do this would be

SELECT CAST(CAST(*DecimalDateFromDB2_Here* + 19000000 AS VARCHAR) AS DATETIME)

Example:

-Showing 2000 + dates

SELECT CAST(CAST(1040526 + 19000000 AS VARCHAR) AS DATETIME)

-Showing 1999 + dates

SELECT CAST(CAST(990526 + 19000000 AS VARCHAR) AS DATETIME)

回答4:

At a recent DB2 for IBM i class I attended, IBM folks were suggesting a pre-populated conversion table, that contains the date values in all the different formats you need to support. You only need 365 or 366 rows per year. You then join to this table to convert. Of course you'll need to create the indexes for each format to have good performance. One guy stated the performing the join could even be faster than calling a conversion function.

Another advantage is that you can add other columns in the conversion table such as the fiscal period, the quarter, business day indicator, etc, which can simplify a bunch of queries, such as finding all orders in the third quarter.

For instance, say the DATECONV table has columns DATEFMT as a real date and CYMDFMT as the decimal date, your select would be SELECT DATEFMT, ... FROM MYTABLE JOIN DATECONV on CYMDFMT=MYDATE

One issue with this solution is that if you use invalid dates as special values such as 9999999 as you can't have invalid dates in the real date field. But then you can't convert them anyway.

回答5:

An [and rather simple] expression verified to function as far back as v5r3, and for which the MYDATE column may be defined as any of CHAR(7), DEC(7), NUMERIC(7), INTEGER or FLOAT data types [or a larger length/precision variant of those types] follows; the DATE casting scalar shown in this example, is converting the result of a character-string expression [per CONCAT] that yields a 14-character string that will be valid as a TIMESTAMP with the format 'YYYYMMDDHHMMSS':

DATE( DEC( 19000000 + MYDATE, 8, 0 ) CONCAT '000000' )

Note: The above expression may be [seen elsewhere] written with a DIGITS casting scalar for the DECIMAL casting result, but that would be superfluous given the addition of a value with a full 8-digits of precision, and implicit cast from numeric to character for the DEC() expression; the DIGITS is required when dealing with dates prior to year 1000 to ensure a leading zero in the string result, or on some even older releases where there is no capability of implicit cast from numeric to character.

IBM i 7.2->Database->Reference->SQL reference->Language elements->Data types->Datetime values->String representations of datetime values->Timestamp strings