Home » SQL & PL/SQL » SQL & PL/SQL » to get different format of dates for the user input
to get different format of dates for the user input [message #229954] Tue, 10 April 2007 07:15 Go to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
Hi,
I need to get the input of date from the user while runtime and show the input of the user given in two different formats

select to_char(to_date('&inputdate'),'day') from dual;
Enter value for inputdate: 01-jan-2007
old 1: select to_char(to_date('&inputdate'),'day') from dual
new 1: select to_char(to_date('01-jan-2007'),'day') from dual

TO_CHAR(T
---------
monday

I need the output as two columns as below

date day
01-jan-2007 monday

is it possible to do with a single query.
Thanks in advance.
Re: to get different format of dates for the user input [message #229955 is a reply to message #229954] Tue, 10 April 2007 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> undef inputdate
SQL> select '&&inputdate', to_char(to_date('&&inputdate'),'day') from dual;
Enter value for inputdate: 01/01/2007

'01/01/200 TO_CHAR(
---------- --------
01/01/2007 lundi

1 row selected.

Regards
Michel
Re: to get different format of dates for the user input [message #230033 is a reply to message #229955] Tue, 10 April 2007 12:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
But what if the person inputs the date in a different format than the default NLS format?
Re: to get different format of dates for the user input [message #230035 is a reply to message #230033] Tue, 10 April 2007 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad for him. Wink
Re: to get different format of dates for the user input [message #230064 is a reply to message #229954] Tue, 10 April 2007 15:50 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
ACCEPT INPUT_DATE DATE FORMAT 'MM/DD/YYYY' PROMPT 'Enter the input date (MM/DD/YYYY):'

select TO_DATE('&inputdate','MM/DD/YYYY') The_Date, to_char(to_date('&inputdate','MM/DD/YYYY'),'day') The_dow from dual;




ACCEPT INPUT_DATE DATE FORMAT 'MM/DD/YYYY' PROMPT 'Enter the input date (MM/DD/YYYY):'
Enter the input date (MM/DD/YYYY):01-JAN-06
SP2-0685: The date "01-JAN-06" is invalid or format mismatched "MM/DD/YYYY"
Enter the input date (MM/DD/YYYY):01/01/2006
Re: to get different format of dates for the user input [message #230114 is a reply to message #230064] Tue, 10 April 2007 23:12 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
The reason which u said will ask twice the user input. I need to ask it only once.

SELECT TO_DATE('&INPUTDATE','MM/DD/YYYY') THE_DATE,TO_CHAR(TO_DATE('&INPUTDATE','MM/DD/YYYY'),'DAY') THE_DOW FROM DUAL;
Enter value for inputdate: 02-JAN-2007
Enter value for inputdate:

Can u suggest some other way.
Re: to get different format of dates for the user input [message #230122 is a reply to message #230114] Tue, 10 April 2007 23:38 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you read my post?
Do you see the exemple?
Did SQL*Plus ask me 2 times?
Don't you notice I used 2 & and not only one?

Regards
Michel
Previous Topic: Update Statement
Next Topic: String validation
Goto Forum:
  


Current Time: Fri Dec 02 17:01:55 CST 2016

Total time taken to generate the page: 0.16133 seconds