All Forums Database
jonesj2 16 posts Joined 02/09
13 Jan 2014
Stored Procedure Default Date Format

We are recompiling stored procedures and some are erroing at execution because they use a different default date format. For example: The current default date format is YYYY-MM-DD, and the SP uses a literal defined as '11/02/2012'. The SP compiles ok but errors when executed. I am assuming the default date format was changed when this SP was compiled. Is the default date format used by the SP kept anywhere where I can retireve it to be sure the re-compiled SP will execute properly?

Raja_KT 1246 posts Joined 07/09
13 Jan 2014

To see the settings in session we use HELP SESSION; 

 

We can set date settings for session:

 

SET SESSION DATEFORM = ANSIDATE; 

 

select date;------to verify the date format

SET SESSION DATEFORM = INTEGERDATE; 

 

Cheers,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

umberger 1 post Joined 09/10
14 Jan 2014

HELP will show the format of the input variable for the store procedure.

HELP PROCEDURE <dbname>.<spname>;

You can also get the information from:

select *
from dbc.columns
where databasename='<dbname>'
and tablename='<spname>'
order by columnid;

 

You must sign in to leave a comment.