• 3 replies
  • Latest Post - ‏2013-01-21T17:03:38Z by ldubois
11 Posts

Pinned topic SQL Stored Procedures and date fields

‏2013-01-21T14:03:06Z |
I've written a SQL stored procedure to retrieve data from a SQL table and return it in a dataset. In the dataset two of the columns are date fields. When I call the stored procedure from iSeries Navigator to test it I get some weird results. What I've been able to determine is the two date fields are causing the weird results. Are date data types not supported in a result set for a SQL stored procedure? The system I'm on is running i5/OS V5R4. Is my only solution to my problem to convert the date fields to character in the result set?
Updated on 2013-01-21T17:03:38Z at 2013-01-21T17:03:38Z by ldubois
  • krmilligan
    450 Posts

    Re: SQL Stored Procedures and date fields

    Date fields are supported in stored procedure result sets. What's the error behavior that you're seeing? Sounds like you should apply the latest V5R4 Database Group PTF and if that doesn't fix the problem, then open a PMR with IBM Support.
  • msoucy
    11 Posts

    Re: SQL Stored Procedures and date fields

    I'm not getting an error. What appears to be happening is everthing seems to be off by four positions following the the two date field. Also the date in the 1st date field looks weird. Instead of it containing the value 01/01/2013 it contains the value 0110-11-01. I'm wondering if there could be some kind of date format mismatch issue. I'm thinking that my stored procedure is wanting a date in one format when it reads the DB2 table and it's getting the date in a different format with a 2-digit year instead of a four digit year. Is there somewhere I need to specify a date format either in my stored procedure or in my DB2 table?
  • ldubois
    122 Posts

    Re: SQL Stored Procedures and date fields

    Date and time formats are stored along with the stored procedure, and the formats used are the ones in effect when the stored procedure is created. In order for you to see them correctly, your client connection attributes should specify the same date/time formats as that used by your stored procedure.

    Starting in 6.1, the date/time formats are passed through to the client interfaces, so it should just work there without having to change your connection attributes. This support requires a 6.1 or later host along with a 6.1 or later client.