CASE - datediff - Conversion failed when converting the varchar value '01-20' to data type int 1

[PabloBB]

Hi All,

I'm a bit of a newbie with SQL and forum posting. I can see the problem but am having trouble getting to the solution!

I'm attempting to group by using a case statement with datediff. Days cancelled before departure date then I want to group by bandings. However it keeps telling me

Conversion failed when converting the varchar value '01-20' to data type int

So I guess that its wants the THEN to be an integer. I can work round it by 01.20, 21.40 etc or just have the start or end banding but ideally I would like it to output
to say '01-20', '21-40' so do i need to convert the THEN bit to a character?

My Code
CASE
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 1 and 20 THEN 20
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 21 and 40 THEN 40
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 41 and 64 THEN 64
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 105 and 125 THEN 125
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 126 and 146 THEN 146
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 147 and 167 THEN 167
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 168 and 188 THEN 188
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 189 and 219 THEN 219
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 220 and 240 THEN 240
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 241 and 261 THEN 261
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 262 and 282 THEN 282
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 283 and 303 THEN 303
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 304 and 324 THEN 324
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 325 and 345 THEN 345
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 346 and 366 THEN 366
ELSE DateDiff(dd,[pnr cancelled date],[pnr departure date key])
END AS DaysCancelled_Grouping


Many many thanks

 

[fredericofonseca]

by the error you are getting it seems the case you shown is not the issue but rather one other field within your query (which you didn't show)

what happens if you do

select distinct CASE
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 1 and 20 THEN 20
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 21 and 40 THEN 40
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 41 and 64 THEN 64
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 105 and 125 THEN 125
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 126 and 146 THEN 146
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 147 and 167 THEN 167
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 168 and 188 THEN 188
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 189 and 219 THEN 219
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 220 and 240 THEN 240
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 241 and 261 THEN 261
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 262 and 282 THEN 282
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 283 and 303 THEN 303
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 304 and 324 THEN 324
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 325 and 345 THEN 345
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 346 and 366 THEN 366
ELSE DateDiff(dd,[pnr cancelled date],[pnr departure date key])
END AS DaysCancelled_Grouping
from your_table

without any other code added to it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

The important thing to realize is that when you use CASE/WHEN, each branch of the when (including the else) should return the same data type. It does not have to, but it should. You see, SQL Server will look at the resulting data type and will use "Data type precedence" to convert them all to the same type.

Basically, if you have a branch that returns an integer and another that returns a string, SQL Server will return an integer (because that is what the data type precedence rules do). In this situation, your else returns an integer because that is what DateDiff returns.

If you want to return strings, you could do this....

CASE
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 1 and 20 THEN '01-20'
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 21 and 40 THEN '21-40'
WHEN DateDiff(dd,[pnr cancelled date],[pnr departure date key]) Between 41 and 64 THEN '41-64'
etc....
ELSE [!]Convert(Varchar(10), [/!]DateDiff(dd,[pnr cancelled date],[pnr departure date key])[!])[/!]
END AS DaysCancelled_Grouping

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PabloBB]

Many thanks fredericofonseca and gmmastros. gmmastros reply has worked a treat. Thanks again