query displays date in diferent formats [message #315028] |
Fri, 18 April 2008 12:52  |
viko
Messages: 9 Registered: March 2008
|
Junior Member |
|
|
Hi
i'm stuck by sql again...
I've a script that's just a big sql query and a little stuff for send it by mail.
Query involves many tables and fields.
Date display mode is defined to be showed as 01-Ene-08 for 01-JAN-08, (it's in spanish). I really don't know where, but I think it should to be setted, since sqlplus works on that way on most cases.(it isn't at NLS_DATE_FORMAT)
Currently, the script is sending the query results with some little errors. It shows ONLY IN A FEW CASES of most of the date fields, the date as '01-JAN-08'
i've check those specific rows in database and those don't appear to be particular.. are dates as any other.
any idea?
thanks in advice
VIKKKKKKKO
|
|
|
Re: query displays date in diferent formats [message #315030 is a reply to message #315028] |
Fri, 18 April 2008 12:56   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
viko wrote on Fri, 18 April 2008 13:52 |
Currently, the script is sending the query results with some little errors. It shows ONLY IN A FEW CASES of most of the date fields, the date as '01-JAN-08'
|
What??????
Maybe the column is a VARCHAR2 field. I am completely lost by this posted message.
|
|
|
|
|
Re: query displays date in diferent formats [message #315448 is a reply to message #315028] |
Mon, 21 April 2008 09:14   |
viko
Messages: 9 Registered: March 2008
|
Junior Member |
|
|
I would like to sorry about my stupidity in this post.
What I thought could be happening, isn't possible.
I realized when trying to do what Michel told me.
I've look for the fields with problems in TOAD
select elt_date from my_table where code_num in (5952092, 5936006, 5931471, 5931472)
ELT_DATE
20/01/2008 02:55:00 p.m.
21/01/2008 02:33:42 p.m.
22/01/2008 01:30:26 p.m.
20/01/2008 06:57:33 p.m.
then I've checked that at sqlplus:
SQL> desc my_table
~~~~~~~~~
ELT_DATE DATE
~~~~~~~~~
SQL> select elt_date from my_table where code_num in (5952092, 5936006, 5931471, 5931472);
ELT_DATE
---------
20-JAN-08
21-JAN-08
22-JAN-08
20-JAN-08
those was supposed to be rows with 'problems', because expected is:
ELT_DATE
---------
20-ENE-08
21-ENE-08
22-ENE-08
20-ENE-08
and the problem's report said not any rows bring the problem... and the reason of that it's just because
current:
select elt_date from my_table where code_num in (5935975, 5935976)
ELT_DATE
28/02/2008 01:25:20 p.m.
28/02/2008 01:25:49 p.m.
> select elt_date from my_table where code_num in (5935975, 5935976);
ELT_FECHA
---------
28-FEB-08
28-FEB-08
this current result is similar to the one to be supposed the expected result (in spanish)
ELT_FECHA
---------
28-FEB-08
28-FEB-08
So, I just need to alter session to spanish at nls_lenguage.
thanks
bye
VIKKKKO
|
|
|
Re: query displays date in diferent formats [message #315460 is a reply to message #315448] |
Mon, 21 April 2008 09:55  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You can also specify the language as the third parameter to to_char:
SCOTT@orcl_11g> select to_char (sysdate, 'dd-mon-yyyy', 'nls_date_language=american') from dual
2 /
TO_CHAR(SYS
-----------
21-apr-2008
SCOTT@orcl_11g> select to_char (sysdate, 'dd-mon-yyyy', 'nls_date_language=spanish') from dual
2 /
TO_CHAR(SYS
-----------
21-abr-2008
SCOTT@orcl_11g>
|
|
|