Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Question
SQL Query Question [message #325340] Thu, 05 June 2008 17:45 Go to next message
NDBoost
Messages: 3
Registered: June 2008
Junior Member
select wonum, description, targcompdate from workorder
where 
(DATE('15-MAY-08')-targcompdate >=90)
and 
status
in (
    'INPRG',
    'WSCH'
    )
and
istask ='0'
and
worktype = 'PM'
ORDER BY TARGCOMPDATE

I am trying to subtract targcompdate from a manually entered date and find the difference. if the difference is >= x days then continue.

If i replace sysdate with date() it works great! but i cant figure how to get it to work with a manually entered date. Any ideas?

edit,
another topic just answered my question.
TO_DATE('15-MAY-08') would be the correct way.

Mods can delete/close this. Thanks anyways

[Updated on: Thu, 05 June 2008 17:51]

Report message to a moderator

Re: SQL Query Question [message #325357 is a reply to message #325340] Fri, 06 June 2008 00:00 Go to previous messageGo to next message
rsree.rsree
Messages: 3
Registered: June 2008
Location: Bangalore
Junior Member

Use to date function as showed below,
select wonum, description, targcompdate from workorder
where
(to_DATE('15-MAY-08','dd-mmm-yy')-targcompdate >=90)
and
status
in (
'INPRG',
'WSCH'
)
and
istask ='0'
and
worktype = 'PM'
ORDER BY TARGCOMPDATE
Re: SQL Query Question [message #325358 is a reply to message #325340] Fri, 06 June 2008 00:05 Go to previous messageGo to next message
rsree.rsree
Messages: 3
Registered: June 2008
Location: Bangalore
Junior Member

Sorry please use to_date('20080515','yyyymmdd') instead of to_DATE('15-MAY-08','dd-mmm-yy')
Re: SQL Query Question [message #325413 is a reply to message #325340] Fri, 06 June 2008 02:45 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
NDBoost
edit,
another topic just answered my question.

TO_DATE('15-MAY-08') would be the correct way.

No, it wouldn't:
SQL> select to_date('15-may-08') from dual;
select to_date('15-may-08') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

TO_DATE requires date format, such as TO_DATE('15-may-08', 'dd-mon-yy'). However, it doesn't guarrantee that it really works. At least, it doesn't for me, in Croatia:
SQL> select to_date('15-may-08', 'dd-mon-yy') from dual;
select to_date('15-may-08', 'dd-mon-yy') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

But this is just fine:
SQL> select to_date('15-may-08', 'dd-mon-yy', 'nls_date_language=english')
  2  from dual;

TO_DATE(
--------
15.05.08

SQL>
Re: SQL Query Question [message #325434 is a reply to message #325413] Fri, 06 June 2008 03:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
... except that a year consists of FOUR digits.
Re: SQL Query Question [message #325442 is a reply to message #325434] Fri, 06 June 2008 04:00 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right!

Except, that won't last long; only 7992 years more and it will be 5 digits we'll be dealing with.
Re: SQL Query Question [message #325461 is a reply to message #325442] Fri, 06 June 2008 04:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
HA! I for one have all my code ready for that.
I created wrappers around every date-function in Oracle, with a configurable number of digits for the years.

My code will continue running on 01-01-10000 AND on 01-01-100000
Re: SQL Query Question [message #325555 is a reply to message #325340] Fri, 06 June 2008 10:04 Go to previous messageGo to next message
NDBoost
Messages: 3
Registered: June 2008
Junior Member
Given that the User Enters the correct Date format "DD-MON-YY" It will work the way i posted it.

Then again not too worried about 1900 years from now..

My next issue to tackle is passing the date from an excel cell, B3.

The folks over at MrExcel.com Gave me this:
Const strFirstPart As String = "select count(*) from maximo.workorder where (TO_DATE('"
Const strLastPart As String = "')-workorder.targcompdate >=90) and workorder.Status in ('INPRG','WSCH) and workorder.istask ='0' and workorder.worktype = 'PM'"
 
'ASSUMING INPUT CELL HAS DATE TEXT IN CORRECT FORMAT - recommend that this is tested in the code.
Debug.Print strFirstPart & Range("A1").Value & strLastPart

[Updated on: Fri, 06 June 2008 10:06]

Report message to a moderator

Re: SQL Query Question [message #325558 is a reply to message #325555] Fri, 06 June 2008 10:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This would open up the possibility to SQL Injection, but I don't know how much you trust the users that will be using this piece of software.
Re: SQL Query Question [message #325559 is a reply to message #325558] Fri, 06 June 2008 10:15 Go to previous messageGo to next message
NDBoost
Messages: 3
Registered: June 2008
Junior Member
Frank wrote on Fri, 06 June 2008 10:13
This would open up the possibility to SQL Injection, but I don't know how much you trust the users that will be using this piece of software.

It will be excel based and the user knows absolutely nothing about SQL or advanced queries. Its an internal thing at my work therefore not too worried about it. They simply type in the DATE in DD-MON-YY format and it reads that field for the DATE.

Re: SQL Query Question [message #325625 is a reply to message #325559] Fri, 06 June 2008 16:55 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
the user knows absolutely nothing about SQL
Some day, you might find yourself very, very surprised.
Re: SQL Query Question [message #325641 is a reply to message #325625] Sat, 07 June 2008 00:14 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Littlefoot wrote on Fri, 06 June 2008 23:55
Quote:
the user knows absolutely nothing about SQL
Some day, you might find yourself very, very surprised.

I'd say you will.

Regards
Michel

Previous Topic: I don't believe if analytic functions do it for me or not (merged with follow-up question)
Next Topic: urgent: changing non-empty column type?
Goto Forum:
  


Current Time: Sun Dec 04 16:45:12 CST 2016

Total time taken to generate the page: 0.10992 seconds