June 17, 2006 at 12:49 pm
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
June 17, 2006 at 11:41 pm
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
June 18, 2006 at 3:50 am
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   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
June 18, 2006 at 9:28 am
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??
June 19, 2006 at 7:45 am
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))
June 22, 2006 at 7:36 pm
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)
-
June 24, 2006 at 11:31 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply