SQL selection with TRANSLATE on current date

Hi,

Goal:  use 'current date' in sql selection with 'between' on a start and end date.  

I'm using the translate to convert current date to ISO format.   The fields stored on the file are 8S 0 ... ccyymmdd .

It runs but incorrect data is selected.   When I change the translate(....) to char(20090521) it return correct information.  

Any help would be greatly appreciated!
select a.cd2xdt, a.cd2ddt, a.cd2rdt, a.cd2cbo, a.cd2crw, a.cd2veh   
  from ccd2fil A exception join busaval b                           
   on(char(a.cd2cbo) = bsawrk and                                   
      char(a.cd2veh) = bsaveh)                                      
   where a.cd2veh <> 0 and                                          
  (TRANSLATE(CHAR(current_DATE, ISO),'-',' ')  between              
       char(A.CD2DDT) AND char(A.cd2rdt))

Open in new window

lynn_harrisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

k_murli_krishnaCommented:
What values are stored in the column you need to stick to same data tye, size and format of data with which you are comparing. If comparing dates in standard/non-standard format but as strings is not okay then convert to date first and that too in correct format and then compare with getdate() or any range based on default or other acceptable date formats.

(TRANSLATE(CHAR(current_DATE, ISO),'-',' ')  between              
       char(A.CD2DDT) AND char(A.cd2rdt))

First of all all three values should be in matching data type, size and format and even then when working with characters compare will be on encoding of database which is ASCII on 32-bit windows. Also, year may be clear but date and month may get interchanged. So, it is best to convert to data all three values and then compare using BETWEEN operator.
0
lynn_harrisAuthor Commented:
Thanks you for your input Kris.

I tried to convert all three to Date,ISO first.  However, it returned error and would not run.  

Do you have a sample you could share?

Thanks, Lynn
0
Gary PattersonVP Technology / Senior Consultant Commented:
First, fix your TRANSLATE function.  As it is coded, it does nothing.  Assuming that you are trying to convert  '-' to ' ', you have parameters two and three backwards.  Then run this query and it should make it clear why you can't compare the two different string formats.

You'll need to construct two string or numeric values in the same format in order to compare.

It is a good practice to get in the habit of making a small test query like this to view the results of conversions to make sure you are getting what you expect.  Most of the time the problem will be very obvious.

- Gary Patterson

-- Bad query with backwards TRANSLATE
select TRANSLATE(CHAR(current_DATE, ISO),'-',' '), char(A.CD2DDT),char(A.cd2rdt)
  from ccd2fil A 
 
-- Fixed TRANSLATE, but embedded blanks still make for incompatible formats for comparison
select TRANSLATE(CHAR(current_DATE, ISO),' ','-'), char(A.CD2DDT),char(A.cd2rdt)
  from ccd2fil A 
 
-- Ugly, but it works
select left(char(current_date,ISO),4)     concat    
       substr(char(current_date,ISO),6,2) concat    
       right(char(current_date,ISO),2) as datestring, char(A.CD2DDT),char(A.cd2rdt)

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

lynn_harrisAuthor Commented:
Thanks You!!

Is there a way to use the DATE(...) format with these and have it work

ie.  

  current Date between date(a.cd2ddt,iso) and date(a.cd2rdt)   ?????
0
lynn_harrisAuthor Commented:
x
0
lynn_harrisAuthor Commented:
thank you
0
Gary PattersonVP Technology / Senior Consultant Commented:
Lynn,

Date() converts a date string (character) or a date serial number (numeric) to a DATE data type.  Check the SQL Reference manual for the formats it understands.  SQL anticipates that dates in database fields are going to be stored as DATE fields.  If they aren't, you have to jump through hoops to get them there.  Conversion of numeric dates to a date serial number is painful (and may not be portable, but I'm not sure), too.

You have a numeric column that is formatted in "sort of" ISO format.  You can use DATE, but first you'd need to convert to character and insert appropriate separators.  RPG can do this conversion from a ccyymmdd numeric to a DATE, but SQL can't do it directly (it understands numerics as an offset number of days from 0001-01-01, with the current Gregorian calendar projected backwards in time, so dates before the adoption of the Gregorian calendar are nonsense dates).

This is a very common problem in AS/400 shops with legacy files containing numeric dates.  I have seen lots and lots of approaches to deal with this:

  • Convert your numeric date fields to DATE format.  This is often a LOT of work.  we did this occasionally during Y2K conversions, though.
  • Add DATE fields to your legacy files and populate them with a trigger program each time the corresponding numeric filed is updated.  Of course, this creats the complexities of managing the trigger programs, and the performance penalties that come with triggers.
  • Create permanent or temporary views of your legacy files that include calculated DATE fields based on numeric date fields.  There is some performance penalty.  Also may require a lot of new views.
  • Create user-defined SQL functions to perform this conversion.  RPG has date functions that handle this conversion in a single, easy line of code.
Other experts may have some other ideas for you, too.  There are LOTS of ways to handle this.

- Gary Patterson
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lynn_harrisAuthor Commented:
Thank you both.  

Gary,   That you for all your input.  I have used the date function a an number of times in RPGLE.   I just couldn't believe SQL didn't handle date conversion better with the numeric fields.  So, I figured I must be missing something.  But it appears it not.      

I really appreciate your insight.  

Lynn
0
lynn_harrisAuthor Commented:
Boy it is late!!!  Sorry for all the missing spellings!
0
Gary PattersonVP Technology / Senior Consultant Commented:
Hey, it's barely dark there.  I'm in Eastern - it is -late- here.

- Gary Patterson
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.