Pinned topic SQL Stored Procedures and date fields
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
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 120000MDWP450 Posts
Re: SQL Stored Procedures and date fields2013-01-21T16:19:20ZThis is the accepted answer. This is the accepted answer.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 110000H7UN11 Posts
Re: SQL Stored Procedures and date fields2013-01-21T16:38:16ZThis is the accepted answer. This is the accepted answer.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 11000095J1122 Posts
Re: SQL Stored Procedures and date fields2013-01-21T17:03:38ZThis is the accepted answer. This is the accepted answer.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.