Stored Procedure Default Date Format

Database
Enthusiast

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?

2 REPLIES
Enthusiast

Re: Stored Procedure Default Date Format

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,

Re: Stored Procedure Default Date Format

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;