|
Rick, Isn't that the sort of thing UDFs are for? As I recall, a UDF can be used much like a BIF within a SQL statement. I haven't tried it, so I may be wrong in my assumptions... <g> Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-898-7863 or ext. 1863 -----Original Message----- From: Chevalier, Rick [mailto:Rick.Chevalier@xxxxxxxxxxxxxxx] Sent: Tuesday, August 12, 2003 2:26 PM To: Midrange Mailing List (E-mail) 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.
midrange.com
