Tweet
I had the same problem, and created a UDF to deal with it. As to how elegant or efficient the "solution" might be I can't say, but it gets the job done. I ran the following from an interactive SQL session: create function date8 (datein date) returns numeric(8,0) language sql deterministic begin return zoned((substr(char(datein,ISO),1,4) concat substr(char(datein,ISO),6,2) concat substr(char(datein,ISO),9,2) ),8,0); end Then I can do something like select * from somefile where date8(current_date) = fielda The above will find all records with fielda (an 8,0 yyyymmdd "date") equal to the current date. Note that creating the function is a one time action.
-
-
Comparing date field to numeric date
Thanks I'll give it a try. -
Comparing date field to numeric date
I am trying to join to files on a store number and a date. The problem is that one file uses an 8,0 YYYYMMDD field and the other is a date field. How do I link them in an SQL statement?Comment
-
Comparing date field to numeric date
My favorite way to extract data is using OPNQRYF in a CL or CLLE program which drives an entire process. Once you get familiar with it, it is very fast to put together and performance has never been an issue for me. The %DATE extraction of Date stamps for conversion to characters in the QRYSLT portion of the statement will convert the time stamp and MAPFLD of numeric fields that are dates to characters converts it to a compatible compare type. Once you have a model, creating additional processes is as quick as copy, paste, and tweak. check out this link to the CL manual for OPNQRYF which also has the links to the DB2 requirements: http://as400bks.rochester.ibm.com/is...3.htm#HDRFDEFDComment
Comment