Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO GET FIRST DATE OF ENTERED DATE
HOW TO GET FIRST DATE OF ENTERED DATE [message #254099] Wed, 25 July 2007 13:58 Go to next message
saicdefg
Messages: 1
Registered: July 2007
Junior Member
HI TO ALL,


I AM TRYING TO GET THE FISRST DATE OF ENTERED DATE.

SQL> SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL;

TRUNC(SYS
---------
01-JUL-07

SQL> SELECT TRUNC(&SYSDATE,'MONTH') FROM DUAL;
Enter value for sysdate: 01-FEB-06
old 1: SELECT TRUNC(&SYSDATE,'MONTH') FROM DUAL
new 1: SELECT TRUNC(01-FEB-06,'MONTH') FROM DUAL
SELECT TRUNC(01-FEB-06,'MONTH') FROM DUAL
*
ERROR at line 1:
ORA-00904: "FEB": invalid identifier


SQL> SELECT TRUNC(&SYSDATE,'MONTH') FROM DUAL;
Enter value for sysdate: 'O1-FEB-06'
old 1: SELECT TRUNC(&SYSDATE,'MONTH') FROM DUAL
new 1: SELECT TRUNC('O1-FEB-06','MONTH') FROM DUAL
SELECT TRUNC('O1-FEB-06','MONTH') FROM DUAL
*
ERROR at line 1:
ORA-01722: invalid number


SQL> SPOOL OFF


WHEN I WANT TO ENTER THE DATE WHICH I WOULD LIKE,I AM GETTING THE ABOVE ERROR
THANK YOU IN ADVANCE
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254100 is a reply to message #254099] Wed, 25 July 2007 14:02 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
1) Read & follow the posting guidelines as stated in the STICKY posts at top of this forum.
2) Get your Caps Lock key fixed.
3) Learn about the correct use of SQL functions by Reading Then Fine SQL Reference Manual
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i88893
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254101 is a reply to message #254099] Wed, 25 July 2007 14:12 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Something like this ?

SQL> select trunc(to_date(&entdate,'YYYYMMDD')) FROM DUAL;
Enter value for entdate: 20070203
old   1: select trunc(to_date(&entdate,'YYYYMMDD')) FROM DUAL
new   1: select trunc(to_date(20070203,'YYYYMMDD')) FROM DUAL

TRUNC(TO_
---------
03-FEB-07

Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254150 is a reply to message #254101] Wed, 25 July 2007 23:25 Go to previous messageGo to next message
ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE
Junior Member
Small correction use MONTH clause to get the first date .

select trunc(to_date(&entdate,'YYYYMMDD'),'MONTH') FROM DUAL;

old1: select trunc(to_date(&entdate,'YYYYMMDD'),'MONTH') FROM DUAL
new1: select trunc(to_date(20070203,'YYYYMMDD'),'MONTH') FROM DUAL

TRUNC(TO_
---------
01-FEB-07
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254234 is a reply to message #254150] Thu, 26 July 2007 04:22 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
select trunc(to_date('&date'),'month') from dual;
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254236 is a reply to message #254234] Thu, 26 July 2007 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! you are able to repeat an answer posted 6 hours before yours.
I'm really impressed.

Regards
Michel
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254237 is a reply to message #254236] Thu, 26 July 2007 04:43 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
see carefully
you will see the difference
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254239 is a reply to message #254237] Thu, 26 July 2007 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh yes, I see now.
You badly copied it and posted a wrong query.
Thanks.

Regards
Michel
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254240 is a reply to message #254239] Thu, 26 July 2007 04:46 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
tell me what is wrong with my query?
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254241 is a reply to message #254240] Thu, 26 July 2007 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select trunc(to_date('&date'),'month') from dual; 
Enter value for date: 10-JUL-2007
select trunc(to_date('10-JUL-2007'),'month') from dual
                     *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254242 is a reply to message #254241] Thu, 26 July 2007 05:01 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
its working fine on oracle 10g
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254246 is a reply to message #254242] Thu, 26 July 2007 05:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
On my Oracle 10g I get the same error Michel got.
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254249 is a reply to message #254246] Thu, 26 July 2007 05:12 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
Look at the attached document
  • Attachment: Doc1.doc
    (Size: 51.00KB, Downloaded 520 times)
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254250 is a reply to message #254249] Thu, 26 July 2007 05:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I will not look at your attachment. Company policy states that I cannot download .DOC, .XLS, .ZIP, ...

I can imagine that it works for you. I can also imagine that it won't work for Michel or Thomas. You did not provide a DATE format. If you use TO_DATE, it is good practice to use a date format.

MHE
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254268 is a reply to message #254242] Thu, 26 July 2007 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It doesn't work for ANY of our databases from 7.1.5 to 10.2.0.3.

Regards
Michel
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254282 is a reply to message #254268] Thu, 26 July 2007 06:30 Go to previous messageGo to next message
ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE
Junior Member
It is working on my Oracle ver 9.2.0.7.0

Select trunc(to_date('&date'),'month') from dual;
Enter value for date: 10-JUL-2007
old 1: select trunc(to_date('&date'),'month') from dual
new 1: select trunc(to_date('10-JUL-2007'),'month') from dual

TRUNC(TO_
---------
01-JUL-07
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254288 is a reply to message #254282] Thu, 26 July 2007 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Enter 10-07-2007 and this will not work.

Regards
Michel
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254294 is a reply to message #254288] Thu, 26 July 2007 06:42 Go to previous messageGo to next message
ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE
Junior Member
This will not work even for query suggested by MarcL
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254300 is a reply to message #254294] Thu, 26 July 2007 06:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, but MarcL's query will always work with a given format of the entered date, regardless of your database/session settings!
Trust us, it is good practice to use explicit dateformats; you WILL regret it if you don't use them.
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254303 is a reply to message #254294] Thu, 26 July 2007 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As Frank said, it will not work but you know why just reading the statement.
With default format, sometimes it will work sometimes not, you'll never know when, you'll never know why in the middle of hundred of queries your client will call you to say: yesterday it worked, today it no more works.

Regards
Michel
Re: HOW TO GET FIRST DATE OF ENTERED DATE [message #254305 is a reply to message #254099] Thu, 26 July 2007 06:54 Go to previous message
ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE
Junior Member
Thanks Frank,Michel for valuble suggestion

Regards
Shivraj
Previous Topic: min(rowid)
Next Topic: How to display output of pl/sql table?
Goto Forum:
  


Current Time: Tue Dec 06 08:49:02 CST 2016

Total time taken to generate the page: 0.07484 seconds