Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2006
    Posts
    12

    Unanswered: Date Year comparison not working in WHERE clause

    Hello,

    I have an Oracle DB background. I have a query where I am trying to fetch records for a particular year but it does brings me some records from other years as well. Here's what I am doing:

    SELECT * FROM TABLE_NAME
    WHERE YEAR (START_DATE) = '2006'

    I have even tried convereted the year to varchar and tried but it still does not work. Please advice.
    Last edited by dbguy15; 07-29-09 at 12:55.

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    BOL

    YEAR

    Returns an integer that represents the year part of a specified date.
    Syntax

    YEAR ( date )

    " ....but it does not brings me some records from other years as well..." sloppy typing doesn't help

    what data_type is the START_DATE ?
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Oct 2006
    Posts
    12
    I modifed the typo.

    the datatype of start_date is datetime.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Alternative:
    Code:
    SELECT * FROM TABLE_NAME
    WHERE START_DATE >= '20060101' AND START_DATE < '20070101'
    This allows the optimiser to use an index on START_DATE (if, of course, the index covered the query or is clustered, which is very unlikely here).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT start_date
    FROM   table_name
    WHERE  Year(start_date) = 2006
    
    SELECT start_date
    FROM   table_name
    WHERE  start_date >= '20060101'
    AND     start_date <  '20091231'
    Can you post the full query that you are using please?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I like all but your less than value George. What's that about?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A mere typo young flump - second date should be 20070101
    George
    Home | Blog

  8. #8
    Join Date
    Oct 2006
    Posts
    12
    It works in group by:

    select col1, year(start_date), count(*) from table_name
    group by col1, year(start_date)
    having year(start_date) in ( '2006', '2007')

    This will give data for 2006 and 2007 only and not others.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dbguy15
    having year(start_date) in ( '2006', '2007')
    eeeeewwwwwww, and also EEEEEEWWWWWWWWWWW

    first, take the quotes off those numbers -- YEAR() produces an integer, so please don't go comparing it to strings

    second, that HAVING clause should be a WHERE clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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