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 - 1640 days ago
Last viewed on: 21 Jul 2017 (2738 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 6 months 21 days 9 hours 16 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 6 months 21 days 9 hours 6 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 6 months 21 days 8 hours 58 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 6 months 21 days 5 hours 53 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 6 months 18 days 5 hours 57 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.