using a Oracle 10g db I have a table something like this:
create table x(
ID NUMBER(10) primary 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.
Generally it's a good idea to post your 4-digit Oracle version and o/s information. Have a look at the Oracle Built-in Data Types in the documentation. You will find there that the "Valid date range from January 1, 4712 BC, to December 31, 9999 AD." in Oracle.
In the calendar, click the desired start date, then click the end date. The selected days are highlighted. OR. Enter start and end dates in the Date Range fields.
sysdate is the current date that is 01/05/2014 in date format DD/MM/YYYY . select 1 from dual WHERE to_date(sysdate,'DD/MM/YYYY') >= TO_DATE('28/02/2014', 'DD/MM/YYYY') AND to_date(sysdate,'DD/MM/YYYY') < TO_DATE('20/06/2014', 'DD/MM/YYYY'); sql.
Use TO_CHAR to display it in any format you like. For example: SELECT TO_CHAR ( TO_DATE (date_value, 'yyyy-mm-dd') , 'mm/dd/yyyy' ) FROM table_x; Things are much easier if you store dates in DATE columns.
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:
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With