Re: Need Help Converting Decimal Date Values to Date Values
- From: Jonathan Ball <jonball@xxxxxxxxxxxxxx>
- Date: Thu, 28 Jun 2007 07:34:38 GMT
walt@xxxxxxxxxxxxx wrote:
I'm using IBM iSeries Access for Windows. My database has a field
named DATETC which contains a decimal value for dates. How can I
convert these decimals to date values? Under SQL Server 2005 I can
convert that decimal value to date values using the following:
DATEADD("d", Fields!DATETC.Value, "1899-12-31"). If I attempt to use
this same command with the iSeries provider I get an error.
If you're doing this in SQL, as your inquiry suggests, then you could either write a user-defined function (UDF) to do it, or you could implement the same code in-line.
Let's assume your dates are in cyymmdd format, where c = 0 if the date is <= 1999, or 1 if the date is >= 2000. Then the code would be something like:
select cast(case left(digits(dec_date_fld),1)
when '0' then '19'
else '20'
end ||
substr(digits(dec_date_fld),2,2) ||
'-' ||
substr(digits(dec_date_fld),4,2) ||
'-' ||
right(char(dec_date_fld),2) as date)
from ...
To turn this into a UDF, you could do something like this:
create function myschema.cvt_dec_date (dec_date dec(7,0))
returns date language sql
return cast(case left(digits(dec_date),1)
when '0' then '19'
else '20'
end ||
substr(digits(dec_date),2,2) ||
'-' ||
substr(digits(dec_date),4,2) ||
'-' ||
right(char(dec_date),2) as date);
Then, in the SQL statement of interest, you'd code
select myschema.cvt_dec_date(dec_date_fld)
from ...
But, beware! The UDF is implemented as a C service program, and it will mean a service program call for every row selected in your query. Those calls have a significant overhead. If you were doing this in an interactive application, and the function were only applied to a relatively small number of rows, say to fill a 15-row subfile, you wouldn't notice much of a performance impact. But if, on the other hand, you were using the UDF in a batch job that read through a substantial number of rows of data, you would notice a significant performance hit.
I tell the iSeries developers in our company not to use UDFs in batch jobs (but it's moot, because none of them codes UDFs or uses any existing ones in their applications.
.
- Follow-Ups:
- References:
- Prev by Date: Re: SQL Query - Help with Joins
- Next by Date: Re: Need Help Converting Decimal Date Values to Date Values
- Previous by thread: Re: Need Help Converting Decimal Date Values to Date Values
- Next by thread: Re: Need Help Converting Decimal Date Values to Date Values
- Index(es):
Relevant Pages
|
