Skip navigation
jenn
jenn
Currently Being Moderated

Year in the where clause

Apr 20, 2010 11:18 AM

I need to get only this year's publications. Here's my where clause:

 

where pubdate = '2010'

 

and it generates an error.  How can I set the criteria to get only this year's results?

 

THansk,

Jenn

255 Views   4 Replies   Latest reply: jenn, Apr 20, 2010 1:49 PM
 
Replies
  • Adam Cameron.
    Currently Being Moderated
    1. Adam Cameron.,
    Apr 20, 2010 11:30 AM   in reply to jenn
    Report

    Ok, if your code errors... make sure to post the error.  Also if you're posting SQL questions, make sure to post the datatype of the columns concerned.  And the DB system you're using.

     

    I suspect your column is a date or timestamp or something?  If so, you cannot compare it to a string, you can only compare the same data types (on the whole... there are some exceptions there, but not relevant to your situation).

     

    Also, if the date value is "2010-04-20" (ie: today), then it's not going to match "2010" is it: because those are two different things.

     

    What you need to do is to consult your DB's docs, and find out if there's a function for extrsacting the year part of a date (in indeed your column is a date), so you can compare like-for-like.  The function name and how it works will be specific to your DB system.

     

    --

    Adam

     
    |
    Mark as:
  • BKBK
    Currently Being Moderated
    2. BKBK,
    Apr 20, 2010 11:35 AM   in reply to jenn
    Report

    where pubdate = 2010


    or

     

    where pubdate = #year(now())#

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    3. Dan Bracuk,
    Apr 20, 2010 12:11 PM   in reply to Adam Cameron.
    Report

    In addition to Adam's answer, and assuming that the datatype is date or timestamp, using functions in the where clause are sometimes slower than using the actual fields.  For your specific example,

     

    where datepart(year, YourDateField) = 2010

     

    might not be as optimal as

    where YourDateField >= 1 jan this year

    and YourDateField < 1 jan next year

     

    Plus, if YourDateField is indexed, you really don't want to use a function.

     
    |
    Mark as:
  • jenn
    Currently Being Moderated
    4. jenn,
    Apr 20, 2010 1:49 PM   in reply to Dan Bracuk
    Report

    Thank you all for answering my question!  I appreciate it!

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points