× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Our third party order entry system is written in Synon/Cool2E/Whatever,
and produces all its date fields in a 7 digit format, CYYMMDD.  The C is
actually a 0 for pre2K dates, a 1 for >=2K dates, and presumably will be
a 2 if we make it to the year 2100.  So today, for example is 1050912.
 
Since the century is essentially the real century minus 19, I've taken
to adding 19000000 to these dates to convert them to CCYYMMDD, then
throwing in some separators to make them ISO.  So 1050912 + 19000000 =
'2005-09-12'. 
 
The trouble comes when I try to use this routine in SQL.  If any of the
fields have a date of zero, then my little equation produces an invalid
date (1900-00-00), and I can't insert it into a date formatted field.
 
So instead, I thought I would produce my output file with all the dates
hardcoded to '0001-01-01', then use an update statement to fetch only
the valid dates and update them, like so:
 
update mksrcw                                
set d2fodt = (select                         
substr(char(EYE1DT + 19000000),1,4) || '-' ||
substr(char(EYE1DT + 19000000),5,2) || '-' ||
substr(char(EYE1DT + 19000000),7,2)          
from mssrc where mksrcw.d2comp = eycomp      
and mksrcw.d2srcd = eysjcd                   
and eye1dt > 0)
 
I figured that last line would prevent any attempt to update the field
if the date were zero, and my '0001-01-01' would just remain in the
field, which would be nice.  However, it apparently tries to update the
field with a NULL value, having not found a record matching all the
criteria.  
 
Is there a way to do this?  
 
Thanks

Greg Fleming 

 

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.