Help with Date fields

[gwilburn]

This may not belong in the RPGLE forum... so forgive me.

In my 20 years on the IBM Power System (and its predecessors) I have nearly always used dates as 8-digit numeric fields (that's just how our ERP and other software did them). However, with some of the new applications we're using it is often better (from the UI perspective) to use actual date fields.

I recently created a table using DDS (ok, shoot me) that contains several date fields (data type L). When adding/updating the file (via SQL in RPGLE) I do not populate several of these fields. In some cases, the user will update the fields (or not). This has not caused a problem in RPG application programs. However, client access will fail to download the file (SQL0181 - Value in date, time, or timestamp string not valid). Another utiility SQL2XLS also fails with CPF5035 Data Mapping Error (18 -- There is data in a date, time, or timestamp field that is not valid.)

Interactive SQL and Run SQL Scripts work fine - replacing the data with pluses (+++++++). The hex value is actually 001A4452.

So I'm not sure how to handle correct this mess. The program logic acts on values of '0001-01-01'. By default the fields are not null capable.

Any ideas would be appreciated - even if they come with a tongue lashing.

[EmmanuelW1]

https://www-304.ibm.com/support/docview.wss?uid=nas8N1014530

Cheers,

Emmanuel

[gwilburn]

https://www-304.ibm.com/support/docv...d=nas8N1014530

Cheers,

Emmanuel

Just figured that out (with help from IBM)... still not sure what's up with the other util SQL2XLS. I've emailed Guiseppe - but the email address was from 2004.

[B.Hauser]

>>Interactive SQL and Run SQL Scripts work fine - replacing the data with pluses (+++++++). The hex value is actually 001A4452.
if you are running STRSQL and/or Run SQL Scripts, try to change the Date Format to any date format with a 4 digit year (ISO, USA, EUR, JIS):
STRSQL --> F13 --> 1 --> DatFmt
Run SQL Script --> Connection --> JDBC Settings --> Format

In Embedded SQL you have to set the date format (for the additional pre-compiler SQL Variables) in the compile command or with a SET OPTION Statement within the source code to any date format with a 4 digit year.

Numeric dates (20160902) need to be converted into a real date (with the RPG function %Date()). SQL can handle "numeric" dates, but the numeric date needs to be the number of days since 0001-01-01.
I.e. Date(736209) will return 2016-09-02.

Birgitta

[gwilburn]

>>Interactive SQL and Run SQL Scripts work fine - replacing the data with pluses (+++++++). The hex value is actually 001A4452.
if you are running STRSQL and/or Run SQL Scripts, try to change the Date Format to any date format with a 4 digit year (ISO, USA, EUR, JIS):
STRSQL --> F13 --> 1 --> DatFmt
Run SQL Script --> Connection --> JDBC Settings --> Format

In Embedded SQL you have to set the date format (for the additional pre-compiler SQL Variables) in the compile command or with a SET OPTION Statement within the source code to any date format with a 4 digit year.

Numeric dates (20160902) need to be converted into a real date (with the RPG function %Date()). SQL can handle "numeric" dates, but the numeric date needs to be the number of days since 0001-01-01.
I.e. Date(736209) will return 2016-09-02.

Birgitta

I have the source code for the SQL2XLS application... I think the issue is related to the DS below, but I don't know enough about embedded SQL to be sure:

     D** Function Template
     D sqlp0100        ds
     D  function                      1    inz('0')
     D  pkgname                      10    inz('SQLPACK')
     D  pkglib                       10    inz('QTEMP')
     D  mainpgm                      10    inz('SQL2XLSR')
     D  mainlib                      10    inz('*LIBL')
     D  stmname                      18    inz('EXCEL')
     D  curname                      18    inz('CURSOR')
     D  openopt                       1    inz(x'00')
     D  claudesc                      1    inz('A')
     D  commit                        1    inz('N')
     D  datefmt                       3    inz('DMY')
     D  datesep                       1    inz('/')
     D  timefmt                       3    inz('HMS')
     D  timesep                       1    inz(':')
     D  namingopt                     3    inz('SYS')
     D  decpos                        1    inz('.')
     D  block                         4B 0 inz(0)
     D  SqlStmtl                      4B 0 inz(0)
     D  SqlStmt                    5000

     D sqformat        ds
     D  format                       10    inz('SQLP0100')

In the code, this is a parameter on QSQPRCED.

I changed the datefmt parameter to 'ISO' and another line where it was converting using %date. It no longer fails, but the date fields in the Excel output contain -693593 (displayed as ######).

[gwilburn]

After some time in debug, I was able to convert the program to use ISO dates.

Thanks for the help