Within the Case condition gets error Conversion failed when converting date and/or time from character string in SQL Server 2012

Question

I have this sql

DECLARE @CurrentLocation geography; 
SET @CurrentLocation  = geography::Point(@Latitude,@Longitude,4326)

SELECT 
    ID,
    RestaurantTitle,
    CuisineName,
    Latitude, Longitude,
    MinimumOrder,
    ImagePath,
    CASE 
       WHEN r.OpeningTimeType IN ('24*7') 
          THEN r.OpeningTimeType 
          ELSE r.Breakfast_OpenTime 
    END AS OpenTime,
    CASE 
       WHEN r.Breakfast_OpenTime > CONVERT (time, CURRENT_TIMESTAMP) 
            AND R.Dinner_CloseTime < CONVERT (time, CURRENT_TIMESTAMP)
          THEN 'PRE ORDER' 
          ELSE 'ORDER NOW' 
    END AS BUTTONNAME,
    ROUND(ROUND(GeoLocation.STDistance(@CurrentLocation), 0) / 1609.34, 0) AS Distance  
FROM 
    [dbo].[tbl_Restaurant1] r 
CROSS APPLY
    (SELECT 
         CuisineName + ',' 
     FROM
         tbl_Cuisine 
     WHERE
         tbl_Cuisine.ID IN (SELECT CuisineID_FK 
                            FROM tbl_Restaurant_Cuisine 
                            WHERE r.ID = RID_FK) 
     FOR XML PATH('')) D (CuisineName)
WHERE 
    @CurrentLocation.STDistance([GeoLocation]) <= 40000

When I ran this SQL, an exception is thrown:

Conversion failed when converting date and/or time from character string

Here are the columns:

ID int
RestaurantTitle varchar(50)
Latitude decimal(10, 6)
Longitude decimal(10, 6)
MinimumOrder int 
ImagePath varchar(50) 
OpeningTimeType varchar(50)
breakfast_OpenTime time

Your first case statement seems to be trying to coalesce a string (r.OpeningTimeType) with a date and/or time (r.Breakfast_OpenTime), which is probably what is producing the error. For a case statement, the values you return need to all be able to be converted to the same data type (the data type with the highest precedence). e.g. '24*7' can't be converted to a date and/or time. — ZLK
(A possible fix is just to cast your r.Breakfast_OpenTime as a varchar in the first case statement). — ZLK
fix that issue by changing first case statement by CASE WHEN r.OpeningTimeType ='24*7' THEN r.OpeningTimeType ELSE CONVERT(varchar(15),CAST(Breakfast_OpenTime AS TIME),100) END AS OpenTime .thanks — Alphy Jose

marc_s marc_s · Accepted Answer · 2017-02-10T05:54:40

A CASE in T-SQL is an expression that returns exactly one atomic value. Therefore, all different options of the CASE must return the same datatype (or at least compatible ones) - and this is not so in your CASE here:

CASE 
   WHEN r.OpeningTimeType IN ('24*7') 
      THEN r.OpeningTimeType 
      ELSE r.Breakfast_OpenTime 
END AS OpenTime,

OpeningTimeType is varchar(50), while Breakfast_OpenTime is of type time. Therefore, SQL Server needs to convert one value into the common return type.

Based on the SQL Server type precedence, it will try to convert both possible return values into TIME and that's where it fails.

So in order to fix this, it's up to you to make sure the CASE variations all return the same datatype so that no implicit conversions need to take place. Since you obviously cannot guarantee that OpeningTimeType can be converted to a TIME, you need to do the opposite - convert the Breakfast_OpenTime to a VARCHAR(50):

CASE 
   WHEN r.OpeningTimeType IN ('24*7') 
      THEN r.OpeningTimeType 
      ELSE CAST(r.Breakfast_OpenTime AS VARCHAR(50))
END AS OpenTime,

Within the Case condition gets error Conversion failed when converting date and/or time from character string in SQL Server 2012

1 Answers