validating a date

[dlstrawn]

when composing an sql for a data source, do you guys know of a way to validate a 6 digit numeric date coming in from a legacy physical file?

[robert.swanson]

I presume you mean "convert" the numeric date to a proper date column, not "validate" since it's already in the file (a bit too late to validate it).

You could try something like this, assuming the numeric date is in YYMMDD format (adjust as needed):

SELECT field1, field2, field3, 
       DATE(TIMESTAMP_FORMAT(CHAR(your_numeric_date),'YYMMDD') as date1
  FROM your_file

[dlstrawn]

I can convert it, but what happens if the date coming in from the physical file is invalid? (i.e. 48/01/05), is there a way to deal with/use default value for those types of dates? I know I can do this with RPG, but was curious if it could be handled in the custom sql statement...

[robert.swanson]

So you've got dirty data in the database, eh? Yikes...

IBM doesn't supply anything to validate dates like that. So you'd have to create your own function. I would suggest you do something like this in STRSQL:

create function QGPL/getDate(
  numDate numeric(6,0))
returns date
language sql
deterministic
Begin
 declare exit handler for sqlexception return null;
 return TO_DATE(char(numDate),'YYMMDD');
End

Then you could trap invalid dates in your select statement and override them to, say, 0001-01-01 as follows:

SELECT field1, field2, field3, coalesce(getDate(your_numeric_date),'0001-01-01') as date1
  FROM your_file

Or you could just omit records with invalid dates entirely via:

SELECT field1, field2, field3, getDate(your_numeric_date) as date1
  FROM your_file
 WHERE getDate(your_numeric_date) IS NOT NULL

Note that TO_DATE in the getDate function here is shorthand for TIMESTAMP_FORMAT, something I just discovered while looking up the function on IBM's website.

[dlstrawn]

Yep, fraid so...

nice! I will play around with this... one other question, I can start a new thread if you want me to... I have a USA formatted char "date" (podate) coming in as a VIEW to the data source where I convert it (cast(podate AS date) AS vv1), but when I show it on the screen in a grid it shows as ISO... How do I change how it shows and not require the user to key in the formatting marks (/ or -) when using date as a "between" filter?

[robert.swanson]

Unfortunately the only way to have a date renderer in the filter is by using a bonafide date column in your data source.

I was inclined to suggest you use that getDate() function in your View to add a "true" date column to use in your widgets, but I think for performance reasons you'd be better served by creating a new generic physical file dedicated to universal date conversion, which you could use for all your various files containing numeric date columns.

So you could call this new file, say, CONVERT_DT, and give it two columns: One column to hold 6 digit numeric dates, and the other for its *ISO equivalent. If you have other date formats in some of your files you could add additional columns to accommodate them as well (i.e., one column in MMDDYY format, one in YYMMDD, etc).

Once this new CONVERT_DT file is created, create a simple one-time-use program to initialize it with all the possible dates between, say, 2000 and 2050.

Then, for any widgets you envision needing date filtering, you could just add this new CONVERT_DT file to the data source (joined on the appropriate numeric date column) and use the *ISO date column in the widget rather than the numeric date column from the original file. This way your widget will have a true date field that can be converted to whatever date format you like, and it would function as desired in the BETWEEN filter you mentioned.

Be sure you create a separate index or logical file over each column in CONVERT_DT, so the SQL engine will automatically grab whichever index/logical it needs for best performance to get to the *ISO date.

[dlstrawn]

so I turned the date into a ISO formatted integer, and am using the between filter that way...they will have to key the date in, but that is what they would want to do anyways...

Thanks for all the examples! they willl come in handy...