Datetime, int comparision in where clause .

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Datetime, int comparision in where clause .

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-23 : 08:15:34

Hi,

I have the following vaiable defined as below. The second declaration below is to be able to select All Maturity from a drop down using another GUI tool. The drop down will display all ranges for Maturity including 'All Maturity'

Declare @Maturity varchar(100)

--For Where Clause for Maturity High and Low
declare @LowMaturity int,
@HighMaturity int

SET @LowMaturity = case @Maturity when '<=1 Year' then 0
when 'All Maturity' then 0
when '>1 Year & <=3 Years' then 1
when '>3 Years & <=5 Years' then 3
else 5
end

SET @HighMaturity = case @Maturity when '<=1' then 1
when 'All Maturity' then 99999999
when '>1 Year & <=3 Years' then 3
when '>3 Years & <=5 Years' then 5
else 99999999
end

I am using the below in my where clause

SELECT
Maturity = CASE
WHEN Datediff(yy,inq.InquiryDate,iss.Maturity) <= 1 THEN '<= 1 Year'
WHEN Datediff(yy,inq.InquiryDate,iss.Maturity) >1 AND Datediff(yy,inq.InquiryDate,iss.Maturity) <= 3 THEN '>1 & <= 3 Years'
WHEN Datediff(yy,inq.InquiryDate,iss.Maturity) > 3 AND Datediff(yy,inq.InquiryDate,iss.Maturity)<= 5 THEN '>3 & <= 5 Years'
WHEN Datediff(yy,inq.InquiryDate,iss.Maturity) > 5 THEN '> 5 Years'
END

WHERE iss.Maturity between @LowMaturity and @HighMaturity

Now iss.Maturity is a datetime field. I get the below error:

Arithmetic overflow error converting expression to data type datetime.

How do I work around this?

Thanks

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-23 : 08:19:39

I din't understand what you want really..can you please show us some sample data and expected output.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-23 : 08:22:56

what is the data type for inq.InquiryDate ?

KH
[spoiler]Time is always against us[/spoiler]

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-23 : 08:28:07

For example: I want to see the data as below.

I used the @HighMaturity and @LowMaturity variables mainly to accomodate the 'All Maturity' value in the drop down selection so users can select this value. I also used the @HighMaturity and @LowMaturity in the where clause to ensure the issue.Maturity value is between the variables. But the issue.Maturity value is a datetime and I am trying to compare a datetime field to an int. Does it make sense to convert the @HighMaturity and @LowMaturity to a datetime in the where clause?

WHERE iss.Maturity between @LowMaturity and @HighMaturity

Maturity
All Maturity
<= 1 Year
>1 & <= 3 Years
>3 & <= 5 Years
> 5 Years

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-23 : 08:32:36

<< khtan>>
ans for this?
what is the data type for inq.InquiryDate ?

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-23 : 08:32:36

Data Type for inq.InquiryDate is also datetime

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-23 : 08:32:44

how do you want to compare iss.Maturity with the 2 variables ?

Number of year difference with reference to the InquiryDate ?


where Datediff(yy,inq.InquiryDate,iss.Maturity) between @LowMaturity and @HighMaturity

KH
[spoiler]Time is always against us[/spoiler]

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-23 : 08:34:53

Yes Number of year difference with reference to the difference between the InquiryDate and MaturityDate and it will be an int like 1 year, 2 years and not a float like 1.5 Years or 2.5 years.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-23 : 08:48:24

Can I use something like Datepart (yy) to get just the two digit year for example is the value for iss.Maturity is

2009-06-29 00:00:00.000, then I can just grab the two digit year part as in 09. Same for inq.InquiryDate. This difference would then be a number?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-23 : 08:50:20

this is not what you wanted ?
" Datediff(yy,inq.InquiryDate,iss.Maturity) between @LowMaturity and @HighMaturity "

KH
[spoiler]Time is always against us[/spoiler]

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-23 : 08:54:08

Yes but when I check teh condition as below:

WHERE iss.Maturity between @LowMaturity and @HighMaturity

I was trying to test the below but the query takes too loong so I figured this wont work.

WHERE datepart(yy, iss.Maturity) between @LowMaturity and @HighMaturity

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-23 : 09:00:17

This worked

WHERE convert(int,datepart(yy, iss.Maturity)) between @LowMaturity and @HighMaturity

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources