oracle date range
using a Oracle 10g db I have a table something like this:
create table x(
ID NUMBER(10) primary开发者_如何学JAVA key,
wedding DATE NOT NULL
);
how can I
select * from x where wedding is in june 2008???
I know it is probably an easy one but I couldn't find any satisfying answer so far. Help is very much appreciated.
Use:
SELECT *
FROM x
WHERE x.wedding BETWEEN TO_DATE('2008-JUN-01', 'YYYY-MON-DD')
AND TO_DATE('2008-JUL-01', 'YYYY-MON-DD')
Use of TO_DATE
constructs a date with a time portion of 00:00:00, which requires the end date to be one day ahead unless you want to use logic to correct the current date to be one second before midnight. Untested:
TO_DATE('2008-JUN-30', 'YYYY-MON-DD') + 1 - (1/(24*60*60))
That should add one day to 30-Jun-2008, and then subtract one second in order to return a final date of 30-Jun-2008 23:59
.
References:
- TO_DATE
This is ANSI SQL, and supported by oracle as of version 9i
SELECT *
FROM x
WHERE EXTRACT(YEAR FROM wedding) = 2008
AND EXTRACT(MONTH FROM wedding) = 06
Classic solution with oracle specific TO_CHAR()
:
SELECT *
FROM x
WHERE TO_CHAR(wedding, 'YYYY-MMM') = '2008-JUN'
(the latter solutions was supported when dinosaurs still walked the earth)
精彩评论