Search by year

Rob1982 · #1 (**permalink**) 12-17-03, 09:55

Hi,

I have created a table in sql plus, and one of my attributes is Date, this has a datatype of DATE.

I want to perfom a query on this attribute by just entering a year i.e. 2003. And returning all the items which have 03 at the end of their date.

Any ideas how to do this? As at the moment I can only enter in the exact date i.e. 21-MAR-03

Cheers

gannet · #2 (**permalink**) 12-17-03, 10:30

You can use TO_CHAR to return the year. In the WHERE clause you will need the following:

Code:

WHERE TO_CHAR (your_date_field, 'YY') = '03'

or 

WHERE TO_CHAR (your_date_field, 'YYYY') = '2003'

This will return all records with 2003 in your date field.

Rob1982 · #3 (**permalink**) 12-17-03, 11:25

Quote:

Originally posted by gannet
You can use TO_CHAR to return the year. In the WHERE clause you will need the following:

Code:

WHERE TO_CHAR (your_date_field, 'YY') = '03'

or 

WHERE TO_CHAR (your_date_field, 'YYYY') = '2003'

This will return all records with 2003 in your date field.

Thanks for the information

andrewst · #4 (**permalink**) 12-18-03, 07:25

Another way would be:

WHERE datecol >= TO_DATE('01-JAN-2003','DD-MON-YYYY')
AND datecol < TO_DATE('01-JAN-2004','DD-MON-YYYY')

(I deliberately avoided using BETWEEN to avoid having to consider time of day).

dendalee · #5 (**permalink**) 04-07-09, 14:17

I want to do the same thing as this post, except:

I have an unbound field which I enter the year I am searching for in the box. I want to see only those records which match the year.

I.e. if I have the following dates:

12/1/2008
3/31/2009
4/1/2009

I want to be able to enter 2009 in the unbound box on a separate form and see ONLY those records from 2009. Any suggestions?

stolze · #6 (**permalink**) 04-07-09, 16:03

Code:

SELECT ...
FROM   ...
WHERE  YEAR(date-column) = ?

Then bind the input to the parameter marker.

JarlH · #7 (**permalink**) 04-08-09, 01:13

And the ANSI SQL way to get the year part out of a date is to use EXTRACT, eg.
...WHERE EXTRACT(YEAR FROM datecolumn) = 2003

(However, just a few DBMS products support this "Intervals and datetime arithmetic" feature of the SQL standard.)

Peter.Vanroose · #8 (**permalink**) 04-25-09, 07:04

Quote:

Originally Posted by gannet

You can use TO_CHAR to return the year:
WHERE TO_CHAR (your_date_field, 'YYYY') = '2003'

This is Oracle-specific. The SQL ANSI standard way is

Code:

WHERE EXTRACT(year FROM your_date_field) = 2003

This syntax is supported by (recent versions of) Oracle (and of any other standards-compliant database system), and should systematically replace and ban out any use of TO_CHAR, imho.

Peter.Vanroose · #9 (**permalink**) 04-25-09, 07:05

Quote:

Originally Posted by stolze

SELECT ...
FROM ...
WHERE YEAR(date-column) = ?

YEAR() is not standard SQL, and is not available in e.g. Oracle.
Also in DB2 and in SQLServer, forget about YEAR(...) and systematically start using EXTRACT(year FROM ...) !

Internet Services

Web Development

Digital Marketing

Consumer Tech