|
Exactly. We've got a couple of UDFs that we wrote here for the exact same reason. I'd be happy to share the code if you need it. Kevin R Mohondro Programmer/Analyst Ashworth, Inc. > -----Original Message----- > From: DeLong, Eric [mailto:EDeLong@xxxxxxxxxxxxxxx] > Sent: Tuesday, August 12, 2003 12:49 PM > To: 'Midrange Systems Technical Discussion' > Subject: RE: SQL date comparison - a better way? > > > 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. > > > _______________________________________________ > 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. > ##################################################################################### Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. Thank You. #####################################################################################
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
