|
midrange.com
I have a recurring issue with SQL statements I create here. I am repeatedly
comparing date values that are stored as numeric fields in MMDDYY format.
The dates are stored in 6,0 and 7.0 fields depending on the data file. I
have managed to come up with a solution but it's pretty complex (ugly?) and
I am wondering if there isn't a better way to do this. I'm attaching a
recent statement as an example of what I'm talking about. Notice all the
repetitive code. Is there a way to build a field once and just reference it
from then on? Is there a better way to convert numeric values to date
fields?
LHPOST = 7,0 MMDDYY
LHEFF = 7,0 MMDDYY
LNNXMT = 6,0 MMDDYY
select lnnote, lnstat, lhtc1,
case char_length(Trim(b from char(LHPOST)))
When 5 then case
When substr(char(LHPOST), 4, 2) >= '40' then
'19' || substr(char(LHPOST), 4, 2) || '-' ||
'0' || substr(char(LHPOST), 1, 1) || '-' ||
substr(char(LHPOST), 2, 2)
When substr(char(LHPOST), 4, 2) < '40' then
'20' || substr(char(LHPOST), 4, 2) || '-' ||
'0' || substr(char(LHPOST), 1, 1) || '-' ||
substr(char(LHPOST), 2, 2)
End
When 6 then case
When substr(char(LHPOST), 5, 2) >= '40' then
'19' || substr(char(LHPOST), 5, 2) || '-' ||
substr(char(LHPOST), 1, 2) || '-' ||
substr(char(LHPOST), 3, 2)
When substr(char(LHPOST), 5, 2) < '40' then
'20' || substr(char(LHPOST), 5, 2) || '-' ||
substr(char(LHPOST), 1, 2) || '-' ||
substr(char(LHPOST), 3, 2)
End
End as Post_Date,
lhamt1, lhrecn,
case char_length(Trim(b from char(LHEFF)))
When 5 then case
When substr(char(LHEFF), 4, 2) >= '40' then
'19' || substr(char(LHEFF), 4, 2) || '-' ||
'0' || substr(char(LHEFF), 1, 1) || '-' ||
substr(char(LHEFF), 2, 2)
When substr(char(LHEFF), 4, 2) < '40' then
'20' || substr(char(LHEFF), 4, 2) || '-' ||
'0' || substr(char(LHEFF), 1, 1) || '-' ||
substr(char(LHEFF), 2, 2)
End
When 6 then case
When substr(char(LHEFF), 5, 2) >= '40' then
'19' || substr(char(LHEFF), 5, 2) || '-' ||
substr(char(LHEFF), 1, 2) || '-' ||
substr(char(LHEFF), 3, 2)
When substr(char(LHEFF), 5, 2) < '40' then
'20' || substr(char(LHEFF), 5, 2) || '-' ||
substr(char(LHEFF), 1, 2) || '-' ||
substr(char(LHEFF), 3, 2)
End
End as Eff_Date,
case char_length(Trim(b from char(lnnxmt)))
When 5 then case
When substr(char(lnnxmt), 4, 2) >= '40' then
'19' || substr(char(lnnxmt), 4, 2) || '-' ||
'0' || substr(char(lnnxmt), 1, 1) || '-' ||
substr(char(lnnxmt), 2, 2)
When substr(char(lnnxmt), 4, 2) < '40' then
'20' || substr(char(lnnxmt), 4, 2) || '-' ||
'0' || substr(char(lnnxmt), 1, 1) || '-' ||
substr(char(lnnxmt), 2, 2)
End
When 6 then case
When substr(char(lnnxmt), 5, 2) >= '40' then
'19' || substr(char(lnnxmt), 5, 2) || '-' ||
substr(char(lnnxmt), 1, 2) || '-' ||
substr(char(lnnxmt), 3, 2)
When substr(char(lnnxmt), 5, 2) < '40' then
'20' || substr(char(lnnxmt), 5, 2) || '-' ||
substr(char(lnnxmt), 1, 2) || '-' ||
substr(char(lnnxmt), 3, 2)
End
End as Nxt_Mat_Dte
from lnp00701 join lnp00301 on lhtc1 = 85 and lhnote = lnnote and
lnstat = ' '
where
case char_length(Trim(b from char(LHEFF)))
When 5 then case
When substr(char(LHEFF), 4, 2) >= '40' then
'19' || substr(char(LHEFF), 4, 2) || '-' ||
'0' || substr(char(LHEFF), 1, 1) || '-' ||
substr(char(LHEFF), 2, 2)
When substr(char(LHEFF), 4, 2) < '40' then
'20' || substr(char(LHEFF), 4, 2) || '-' ||
'0' || substr(char(LHEFF), 1, 1) || '-' ||
substr(char(LHEFF), 2, 2)
End
When 6 then case
When substr(char(LHEFF), 5, 2) >= '40' then
'19' || substr(char(LHEFF), 5, 2) || '-' ||
substr(char(LHEFF), 1, 2) || '-' ||
substr(char(LHEFF), 3, 2)
When substr(char(LHEFF), 5, 2) < '40' then
'20' || substr(char(LHEFF), 5, 2) || '-' ||
substr(char(LHEFF), 1, 2) || '-' ||
substr(char(LHEFF), 3, 2)
End
End
>
case char_length(Trim(b from char(lnnxmt)))
When 5 then case
When substr(char(lnnxmt), 4, 2) >= '40' then
'19' || substr(char(lnnxmt), 4, 2) || '-' ||
'0' || substr(char(lnnxmt), 1, 1) || '-' ||
substr(char(lnnxmt), 2, 2)
When substr(char(lnnxmt), 4, 2) < '40' then
'20' || substr(char(lnnxmt), 4, 2) || '-' ||
'0' || substr(char(lnnxmt), 1, 1) || '-' ||
substr(char(lnnxmt), 2, 2)
End
When 6 then case
When substr(char(lnnxmt), 5, 2) >= '40' then
'20' || substr(char(lnnxmt), 4, 2) || '-' ||
'0' || substr(char(lnnxmt), 1, 1) || '-' ||
substr(char(lnnxmt), 2, 2)
End
When 6 then case
When substr(char(lnnxmt), 5, 2) >= '40' then
'19' || substr(char(lnnxmt), 5, 2) || '-' ||
substr(char(lnnxmt), 1, 2) || '-' ||
substr(char(lnnxmt), 3, 2)
When substr(char(lnnxmt), 5, 2) < '40' then
'20' || substr(char(lnnxmt), 5, 2) || '-' ||
substr(char(lnnxmt), 1, 2) || '-' ||
substr(char(lnnxmt), 3, 2)
End
End
Rick
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
