datediff using datatime fields

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)

datediff using datatime fields

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-19 : 11:34:27

Hi

I am doing the below query for example:

select Maturity = CASE
WHEN Datediff(yy,inq.InquiryDate,iss.Maturity) <= 1 THEN '<= 1 Year'
END

inq.InquiryDate and iss.Maturity are both datetime fields.

I checked the foll query and it apparentely returns an int.

declare @InquiryDate datetime
declare @Maturity datetime

set @InquiryDate = '2005-03-15'
set @Maturity = '2010-03-15'

select convert(varchar(100),Datediff(yy,@InquiryDate,@Maturity))

So I applied the same but it still gives me an error.

What am I doing wrong?

Thanks

I get the below error

Conversion failed when converting the varchar value '<= 1 Year' to data type int.

I am trying to store the int value when in fact I should store a varchar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 11:45:39

you cant store '<= 1 Year' in int field as with < & year its no longer int. if your intention is to store int what's the purpose of case when with hardcoded values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mfemenel
Professor Frink

1421 Posts

Posted - 2010-03-19 : 11:45:40

What data type is the maturity field you're assigning the output of the case statement to?

Mike
"oh, that monkey is going to pay"

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-19 : 13:20:43

Maturity is varchar(100).

Also I have a parameter key table that stores values to be passed to a GUI tool. Maturity is being passed as a parameter to the stored proc and will appear as <= 1 Year, >1 & <= 3 Years and so on.

key value

0 None
1 <= 1 Year
2 >1 & <= 3 Years
3 >3 & <= 5 Years
4 > 5 Years

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 13:40:41

and are you comparing this to datediff thingy?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-19 : 14:03:20

Yes. I also tried modifying the key table values as below and the query to take varchar values.

Key Value

None None
<= 1 Year <= 1 Year
>1 & <= 3 >1 & <= 3 Years
>3 & <= 5 >3 & <= 5 Years
> 5 Years > 5 Years

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

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-19 : 14:06:58

I used the WHEN convert(varchar(100),Datediff(yy,inq.InquiryDate,iss.Maturity)) '<=1' THEN '<= 1'

This did not work either. Get the below error.

Msg 102, Level 15, State 1, Line 173
Incorrect syntax near '<= 1'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 14:12:36

it should be


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-19 : 14:31:35

I tried the above and I still get the error:

Msg 245, Level 16, State 1, Line 85
Conversion failed when converting the varchar value '<= 1 Year' to data type int.

Then I tried using the convert(varchar(50), ) but this still gives the same error.

Maturity = CASE
WHEN convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) <= 1 THEN '<= 1 Year'
WHEN convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) >1 AND convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) <= 3 THEN '>1 & <= 3 Years'
WHEN convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) >3 AND convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) <= 5 THEN '>3 & <= 5 Years'
WHEN convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) > 5 THEN '> 5 Years'
END
maturity is a varchar. The result for the datediff is an int. I am not sure how to fix this.

Thanks.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-19 : 15:00:51

I think it may have to do with the variable definition?

Should I define Maturity as datetime or int instead of varchar?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-19 : 15:20:05

Just a guess:

CASE
	WHEN convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) <= 1 
		THEN 1
	WHEN convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) >1 AND convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) <= 3 
		THEN 2
	WHEN convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) >3 AND convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) <= 5 
		THEN 3
	WHEN convert(varchar(50),Datediff(yy,inq.InquiryDate,iss.Maturity)) > 5 
		THEN 5
	ELSE 0
END AS Maturity

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-19 : 16:36:33

I figured there was anotehr problem with the query. The syntax you sent did work.

Question: When does an error like the below occur:

(3509 row(s) affected)
Msg 244, Level 16, State 1, Line 158
The conversion of the varchar value '154247' overflowed an INT1 column. Use a larger integer column.

I am passing a value 154247 to a variable defined as varchar(50).

I get the error when runing the below query.

The problem lies in the where clause

where and iql.ProductID = @Product

@Product is defined as a varchar(50). iql.ProductID is a tinyint. This check is probably wrong. I want to check for the values passed in @Product. The values are diven by the selection of a combo box allowing multi select and the GUI tool gets the values fromt the parameter key table. When I hard code the values it works. The values are as below.

key value
1 High Grade
2 Floating Rate Notes
3 Emerging Markets
30 Emerging Local Markets
4 High Yield
40 High Yield Crossover
6 US Agency
7 US Agency FRN
41 US FDIC
42 US FDIC FRN

SELECT DISTINCT
MISInquiryID = inq.MISInquiryID,
--ProductID = iql.ProductID,
Product = prd.Description,
SizeOrder = CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
SizeBucket = CASE
WHEN iql.USDSize <= 1000 THEN '<=1MM'
WHEN iql.USDSize > 1000 THEN '>1MM'
END,
DealerCnt = COUNT(DISTINCT res.CPPivotalCompanyID),
ResponseCnt = COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END)
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
--INTO #ResponseCnt
FROM
Inquiry inq
INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID
INNER JOIN
Product prd
ON
prd.ProductID = iql.ProductID
INNER JOIN
Issue iss
ON
iss.MISIssueID = iql.InstrumentMISIssueID
INNER JOIN
Response res
ON
res.MISInquiryID = iql.MISInquiryID
INNER JOIN
ResponseLeg rsl
ON
res.MISResponseID = rsl.MISResponseID
and iql.MISInquiryID = rsl.MISInquiryID
and iql.LegSequence = rsl.LegSequence
INNER JOIN
pivotal..Company dlr
ON
res.CPPivotalCompanyID = dlr.Company_Id

WHERE
iql.IsVolume = 1
and inq.IsVolume = 1
and inq.InquiryDate BETWEEN @StartDate and @EndOfEndDate
--and inq.InquiryType = 'F'
--and iql.State = 'T'
and iql.ProductID = @Product--in (1,2,6,7,4,40)
and iql.USDSize between @LowSize and @HighSize

GROUP BY
CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
CASE
WHEN iql.USDSize <= 1000 THEN '<=1MM'
WHEN iql.USDSize > 1000 THEN '>1MM'
END,
inq.MISInquiryID,
iql.ProductID,
prd.Description
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 01:16:00

try passing values as csv and then use something like

where ',' + @Product + ',' like '%,' + cast(iql.ProductID as varchar(10)) + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources