CODEWITHSUNDEEP
sql-server
Alphy Jose
Alphy Jose

Reputation: 201

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

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

Upvotes: 1

Views: 1672

Answers (1)

marc_s
marc_s

Reputation: 754518

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,

Upvotes: 1

Related Questions