Re: NLS_DATE_FORMAT and Stored Proc

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:2wChd.285807$wV.281844_at_attbi_s54...
|
| "swatir" <swatir_2003_at_yahoo.com> wrote in message
| news:ea944b33.0411011400.37e96c9f_at_posting.google.com...
| > Hi,
| >
| > I am sending a date time as a string to a stored proc. The stored proc
| > compares this date time value to the date time value in a column in
| > the DB. This comparison is failing due to the formats of the date
| > time. The string I am sending in is of this format - "01/01/1976
| > 12:30:00".
| >
| > I also have an entry in my .bashrc -
| > export NLS_DATE_FORMAT="mm/dd/yyyy hh24:mi:ss".
| >
| > The stored proc however reads the date time value from the column in
| > the oracle default format "DD-MON-YY" and fails on the comparison.
| >
| > Any help is appreciated.
| Don't send a date in as a string, send it in as a date, convert the string
| to a date explicitly using to_date(the string, the string format)
| Jim
|
|

don't rely on environment settings for implicit data conversions -- if you do get your code to work, and then the environment changes, your code breaks

your code needs to take responsibility for date formats; depending on your environment you will want to guarantee that your code always issues an ALTER SESSION command at startup (connect), or you will always want to use explicit conversions whenever it is necessary to represent a date value as a character value

++ mcs Received on Tue Nov 02 2004 - 06:02:11 CST