Home » SQL & PL/SQL » SQL & PL/SQL » Date format changes while fetching the record in a cursor (PL/SQL)
Date format changes while fetching the record in a cursor [message #308050] Thu, 20 March 2008 18:46 Go to next message
harshnayak
Messages: 12
Registered: July 2007
Location: Los Angeles
Junior Member
The date in our table is stored using the oracle DATE data type. It is stored in MM/DD/YYYY format. Now, when I am trying to fetch this date column using a cursor, the variable (DATE type inside pl/sql code) that takes this date changes the format to DD-MON-YY. How can I fetch the date in exactly the same manner as it is in the table?
Re: Date format changes while fetching the record in a cursor [message #308051 is a reply to message #308050] Thu, 20 March 2008 18:48 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
> It is stored in MM/DD/YYYY format
NO, it is NOT stored in ANY format.
A date datatype can be presented in any format you desire by using TO_CHAR function or by setting NLS_DATE_FORMAT

[Updated on: Thu, 20 March 2008 20:35] by Moderator

Report message to a moderator

Re: Date format changes while fetching the record in a cursor [message #308052 is a reply to message #308051] Thu, 20 March 2008 19:04 Go to previous messageGo to next message
harshnayak
Messages: 12
Registered: July 2007
Location: Los Angeles
Junior Member
What I meant was, we stored it in MM/DD/YYYY format in the table. And the NLS_DATE_FORMAT has been set to DD-MON-YYYY. But, when I fetch the record, I lose the YYYY information. So a date which is stored as '12/12/2067' in the database becomes '12-DEC-67' when I fetch it. And then it is interpreted as 12-DEC-1967.
Re: Date format changes while fetching the record in a cursor [message #308053 is a reply to message #308050] Thu, 20 March 2008 20:10 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
'this is string 12/12/2067 not a date'
'12/12/2067' & this is string; not a date
'2008-03-20' & this is string; not a date

With Oracle, STRINGS are delimited by single quote marks.

Date datatypes have NO format; NONE within the database!

I challenge you use SQL*PLUS to CUT & PASTE the whole session which demonstrates your claims, statements & "problem".

Everyone would benefit if you REALLY read & follow the posting guidelines as stated in URL below:

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Thu, 20 March 2008 20:28] by Moderator

Report message to a moderator

Re: Date format changes while fetching the record in a cursor [message #308082 is a reply to message #308050] Fri, 21 March 2008 01:37 Go to previous message
Michel Cadot
Messages: 64127
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Study these 2 Oracle functions and related format:
TO_DATE
TO_CHAR

Regards
Michel
Previous Topic: PLSQL - JAVA Integration
Next Topic: ora-03001 unimplemented feature
Goto Forum:
  


Current Time: Wed Dec 07 06:46:50 CST 2016

Total time taken to generate the page: 0.08622 seconds