|
midrange.com
SEQUEL from ASC (www.asc-iseries.com). Beautiful little function
called CVTDATE. It handles all types of ways to store dates.
(on soapbox)
If you're not using it, you're doing way too much work.
(off soapbox)
Paul Nelson
Arbor Solutions, Inc.
708-670-6978 Cell
pnelson@xxxxxxxxxx
"Chevalier, Rick" <Rick.Chevalier@xxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
08/12/2003 02:26 PM
Please respond to Midrange Systems Technical Discussion
To: "Midrange Mailing List (E-mail)" <midrange-l@xxxxxxxxxxxx>
cc:
Subject: SQL date comparison - a better way?
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
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 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.
