field results for date fomat 03/28/03 to 03/28/2003 1

[ISCIO]

In a query, our date field is yy/mm/dd, can it be arranged in the query to reflect mm/dd/yyyy? If so, how?

any help would be appreciated by me.

 

[RedMage1967]

How is your date data stored in the file? Is it a numeric or Character field? RedMage1967
IBM Certifed - RPG IV Progammer

 

[ISCIO]

The date field is stored numeric

 

[RedMage1967]

That might've blown my idea then. I was thinking if the date was CHAR, you could substring out the month, day, year, and then concat them back together along with the '20' to make the year 2003. But, then again, that would work with a year of '99' and earlier. The only other way I know how to change the date format is in a RPG 4 program. RedMage1967
IBM Certifed - RPG IV Progammer

 

[gpzcrasher]

Although RedMAge1967 is correct about his idea not working, you can convert a numeric field to char by using the digits operator when defining a result field. However as far as I know there is no way to convert a date from one format to another in query. --------------------------------
If it ain't broke, don't fix it!

 

[RedMage1967]

I just thought of something. There might be a way to do this using multiple queries. You would have to convert the date into a char field, them substring out the month, day and year. Have your query first of all omit years that have '00', '01', '02', '03'. (This is assuming that you don't have any dates referring to 2004.) The have the query put the date back together as month/day/'19'year. Select which fields you want in the report, and have the data saved to a file. Copy this query into a new query, put have this query SELECT years '00','01','02','03'. Then, when you put the date back together, change the '19' to '20'. Have this query add the data to the file from query 1. Then write a query to create the report you want, based on the new file. Does this make sense? RedMage1967
IBM Certifed - RPG IV Progammer

 

[GBRPG]

If it wasn't for adding the century to the date, it can be done by converting the date with the digits operator and then concatenating the date in correct format with a series of substring operands. Is it a necessary requirement that the date be M/D/CY?

 

[ISCIO]

Thank you!

It is not necessary for m/d/cy.. The user is confused by the yy/mm/dd format.

 

[charliehummel]

create a copy of the DDS and change the field containing the date to a date field which will put it in *ISO. then by copying the live file into the new file the date format will be changed from yy/mm//dd to *iso. run query over new file. the origional date field must be defined as zoned numeric. You can create this temp file in qtemp as a lead in to the query function. if the field is packed or char you will have to first change field to zoned numeric. Only one problem bad dates will cause a conversion error on copy.

 

[iSeriesCodePoet]

Just as a hint, unless SQL knows that it is a date field YYMMDD is a better format for searching and sorting. Then it is in order.

iSeriesCodePoet
IBM iSeries (AS/400) Programmer

Want to have all your bookmarks in one spot?

http://www.koldark.net/bookmark4u/