Where clause with variable

  • Hai all, I need some advise from you all and thank before

    I have accbalance table, structure below:

    No.(char 3) myYear(decimal 4,0) Balance(money)

    001  2006   1000

    001  2007   2000

    001  2008   3000

    002  2006   4000

    002  2007   5000

    003  2008   6000

    I have datetime parameter @date

    I need to select records with year equal year from @date parameter

    command:

    select * from accBalance where myYear = year(@date)

    but command failed with error :

    failed to convert value from datetime to decimal

    when i change @date with getdate(), command run properly

    Please advise... thanks

    Regards,

    Sandi Antono

  • Hi - you needn't cross post as we see all new messages in all of the forums anyway.

    Thread is continued here (http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=288343) already....

  • The problem is that the 'myYear' field is a decimal type, and the YEAR() function gives an integer. If you'll only ever use a full year in the myYear field, you might as well convert it to an integer. Or else if you really do need the field to be a decimal, you can use CAST() or CONVERT() around the YEAR() function to convert it to a decimal too.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply