Results 1 to 9 of 9

Thread: Search by year

  1. #1
    Join Date
    Dec 2003
    Posts
    22

    Unanswered: Search by year

    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

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    22

    Smile

    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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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).

  5. #5
    Join Date
    Dec 2003
    Posts
    138

    Similar Question

    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?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Code:
    SELECT ...
    FROM   ...
    WHERE  YEAR(date-column) = ?
    Then bind the input to the parameter marker.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    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.)

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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 ...) !
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •