Where clause with variable

Question

Where clause with variable

Sandi Antono

SSC Enthusiast

Points: 124
More actions
June 17, 2006 at 12:49 pm

#168693

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

Viewing 7 posts - 1 through 6 (of 6 total)

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

Keith Risman SSC Veteran Points: 287 More actions · Answer 1

The following procedure works. You need to Cast the datetime variable @date to decimal to fit the datatype in your accbalance table.

Alter

procedure SelectByYear

@date

as smalldatetime

As

Select

no, myyear, balance from

accbalance

Where

myyear = Cast(@date as decimal)

exec

selectbyyear 2008

Keith Risman

Ian Yates SSCoach Points: 19738 More actions · Answer 2

The following code works - how is yours different? The year function is exactly what you want to use.

You do not want to cast the datetime variable to decimal... DateTimes are stored as a number of days since 1Jan1900 (although I routinely get date 0 = 30/12/1899? Ignore that anyhow ). Times of the day are stored as a fraction of a day. Thus converting to a decimal will not give a year but will give a number of days.

create table #x(
 [no] char(3),
 myYear decimal(4,0),
  balance money
&nbsp
GO

insert into #x([no], myYear, balance)
select
'001',  2006,   1000
UNION ALL select
'001',  2007,   2000
UNION ALL select
'001',  2008,   3000
UNION ALL select
'002',  2006,   4000
UNION ALL select
'002',  2007,   5000
UNION ALL select
'003',  2008,   6000
GO

declare @date datetime
set @date='2006-06-20'

select * from #x
where myYear=year(@date)
GO

drop table #x

Sandi Antono SSC Enthusiast Points: 124 More actions · Answer 3

It's working when run from stor procedure by try this one:

right click on the table and choise open table

put the code and run.....???? or create dataadapter on c# put the select then preview....?????

bug??? or something??

vadba SSChampion Points: 11133 More actions · Answer 4

Ian's code works fine on my SQL Server 2000 SP4.

If your interface is complaining about comparing int with Decimal(4,0), cast the Year() function to Decimal(4,0):

select *

from accBalance

where myYear = Cast(Year(@date) as Decimal(4,0))

Jason- SSCrazy Points: 2764 More actions · Answer 5

There appears to be problem with the actual data stored in the variable @date. I ran the below statement and it works just fine...

Can you post the results of "Select @date"

This works (it's the same as what was originally posted as problem.)

create table tblTest (Num char(3),MyYear decimal(4,0),balance money)

insert into tbltest select '001',2006,20.98

insert into tbltest select '002',2006,201.98

insert into tbltest select '003',2003,203.98

insert into tbltest select '004',2002,204.98

insert into tbltest select '005',2001,205.98

insert into tbltest select '006',2000,206.98

DECLARE @date datetime

set @date = '2006/06/22'

select * from tblTest where MyYear = year(@date)

-

Sandi Antono SSC Enthusiast Points: 124 More actions · Answer 6

Thank to you all guys,

Maybe i must install latest service pack first (i using sql server 2005)

for temporary i used this command:

CAST

(year AS decimal) = CAST(YEAR(@date)

regards,

Sandi Antono