Midrange News for the IBM i Community


Posted by: sarge
Citizen/Soldier
U.S. Army
United States
dates
has no ratings.
Published: 31 Dec 2012
Revised: 23 Jan 2013 - 1611 days ago
Last viewed on: 21 Jun 2017 (2678 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

dates Published by: sarge on 31 Dec 2012 view comments(5)

hi all,

i have a date field in a file, that is 6.0 ymd format. i added an *iso date field to that file and wrote a simple rpg program to populate the new field.

using SQL, i viewed the file. it showed the date field being 01/01/81. after several frustrating rewrites of a 3 line RPG program, i used UPDDTA to look at the records. all of the records are in the proper 1981-01-01 format.

any ideas as to why the data is populated correctly, but SQL shows the date in mm/dd/yy but UPDDTA shows it as ccyy-mm-dd as proper? based on this, how do i prove that the data in the file is correct or not?

thanks.

-sarge

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: dates
Posted: 4 years 5 months 21 days 22 hours 32 minutes ago

If you are using the green screen SQL interface (STRSQL), then the date format that dates are displayed in can be changed using F13=Services and take option 1=Change session attributes.

Posted by: sarge
Premium member *
United States
Comment on: dates
Posted: 4 years 5 months 21 days 22 hours 21 minutes ago

okay. never used that feature before, in StrSql. for whatever reason, i expected the display to show the date in the format, defined by the PF.

lives and learns.

thanks.

-sarge

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: dates
Posted: 4 years 5 months 21 days 22 hours 13 minutes ago

Dates, in RPG, come in as *ISO format regardless of how they are defined in the externally described files. Seeing the date as an *ISO format in debug is correct, typically.

You can modify it by setting the default date format on the File or at the Field-level but most people don't find that feature too intuitive.

I typically use DSPPFM to show the data in the file. However remember, that all interfaces, including SQL extract the date from its 4-byte integer internal format and translate it to the DATFMT specified on the field definition or to the host HLL default date format.

Posted by: sarge
Premium member *
United States
Comment on: dates
Posted: 4 years 5 months 21 days 19 hours 9 minutes ago

agreed, bob. and i would normally use DSPPFM myself, but the radical in me decided to browse using SQL and got the suspect looking data. silly me for tyring to be innovative...... :)

-sarge

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: dates
Posted: 4 years 5 months 18 days 19 hours 13 minutes ago

I find it best to update your system sql related commands to use *ISO, then there's no problem. This includes compile SQLRPGLE and strsql.