April 11, 2011 at 2:44 pm
i am trying to access a view in oracle an based on one of the column (released_date) i need to extract report.
released_date in db is stored as datetime.
i have added two parameters as string and need to retrieve data between start and end dates.
Below is my query and i get an error
ORA-00911: invalid character
(System.Data.OracleClient)
select * from db.view where TRUNC(TO_DATE(released_date, 'mm/dd/yyyy')) <= Format(:startdate,”dd/MM/yyyy”) andTRUNC(TO_DATE(released_date, 'mm/dd/yyyy')) >= Format(:enddate,”dd/MM/yyyy”)
Please let me know what i am doing wrong.
April 12, 2011 at 6:59 am
I don't know about Oracle but I would write it like this:
select * from db.view where TRUNC(TO_DATE(released_date, 'mm/dd/yyyy')) BETWEEN Format(:startdate,”dd/MM/yyyy”) AND Format(:enddate,”dd/MM/yyyy”)
November 16, 2011 at 9:43 am
I don't believe there is a Format function in Oracle SQL for datetime fields.
If your released_date field is already a datetime type, you should not need to enter a format string.
The TRUNC function just truncates the datetime to midnight.
Try:
select *
from db.view
where TRUNC(released_date) <= :startdate
and TRUNC(released_date) >= :enddate
This is assuming you have declared the start & end date parameters as datetime.
Sometimes TRIM is also used, if TRUNC does not get the desired result.
hth
jc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply