Home » SQL & PL/SQL » SQL & PL/SQL » query displays date in diferent formats (sqlplus on solais)
query displays date in diferent formats [message #315028] Fri, 18 April 2008 12:52 Go to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 different formats [message #315031 is a reply to message #315030] Fri, 18 April 2008 13:04 Go to previous messageGo to next message
viko
Messages: 9
Registered: March 2008
Junior Member
Joy Div:

let me explain:

query results shows only some rows of the date_type columns in english format instead of the spanish format


Re: query displays date in different formats [message #315046 is a reply to message #315031] Fri, 18 April 2008 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post description of the table and select with wrong and correct output.

Regards
Michel
Re: query displays date in diferent formats [message #315448 is a reply to message #315028] Mon, 21 April 2008 09:14 Go to previous messageGo to next message
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 Go to previous message
Barbara Boehmer
Messages: 8621
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>
Previous Topic: Display column_name and column_value
Next Topic: Optimal plan Versus Execution plan
Goto Forum:
  


Current Time: Fri Dec 02 23:20:28 CST 2016

Total time taken to generate the page: 0.09788 seconds