开发者

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)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜