SQL Date Functions for Oracle

[jlindahl]

My ASP page needs to query the oracle database looking for a date that meets a particular month and year. I can't seem to use the MONTH nor YEAR functions in SQL. Is there a way around this?

 

[BJCooperIT]

Have you tried:

select *
from   mytable
where  to_char(mydate,'MMYYYY') = '012004';

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[jlindahl]

That won't work because the oracle field is a date datatype. Since I couldn't get other functions to work, i managed with this:

dateSelected = request.form("month") & "/01/" & request.form("year")
dim sql, RS
sql = "select * from oracle.table " &_
	"where (TRUNC(lastuserdate) >= To_Date('" & dateSelected & "','MM/DD/YYYY') " &_
	&quot;AND TRUNC(lastuserdate) <= Last_Day(To_Date('&quot; & dateSelected & &quot;','MM/DD/YYYY'))) &quot;

Thanks for your post though.

 

[Cagliostro]

Hey man, the responce above to your question is correct.
After creating dinamically the SQL put a MsgBox sql to see what is inside the sql. By the way, I've found many samples in different books whith dinamy sql, but I see nothing more stupid than dinamyc SQL. What kind of API are you using to access Oracle?


Ion Filipski

 

[Cagliostro]

by the way, if you use OO4O, I advise you to use this syntax:

select * from oracle.table
where
lastuserdate
between
To_DatedateSelected, 'MM/DD/YYYY')
AND
Last_Day(To_DatedateSelected, 'MM/DD/YYYY'))

And bind the parameter dateSelected to a string.

Ion Filipski

 

[dnoeth]

Try Standard SQL:
EXTRACT(YEAR FROM dateselected)
EXTRACT(MONTH FROM dateselected)

IIRC it's included since Oracle 8i

Dieter

 

[jlindahl]

wow, lots of responses. that's great! so BJCooperIT's solution will work, even if the oracle datatype is a date with the date and time stamp?

Ion Filipski, I am using OO4O. What syntax are you talking about, the BETWEEN? I think when I use BETWEEN, I'm not getting the last date included. I think something was going on, so I decided to use the <= & >= method.