Convert day time string to time-CodePudding

I want to convert a column containing day hour minute and second to time (01 03:08:09) in SQL Server 2014 because it is in the nvarchar(255) data type, so that I can use the DATEDIFF function and I get an error message:

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

I tried using;

SELECT CAST (Driver_at_restaurant_datetime AS time)
FROM deliveries

How do I resolve this?

CodePudding user response：

01 03:08:09 is not a valid time, so it will produce an error. You need to extract 03:08:09 first with right() then convert it to a time as follows:

SELECT CAST (right(Driver_at_restaurant_datetime,8) AS time)
FROM deliveries

CodePudding user response：

CONVERT(DATETIME,'1900-01-'   CONVERT(CHAR(2),CAST (LEFT(Driver_at_restauant,2) AS INT)   1)   RIGHT(Driver_at_restaurant,9),120)

This will convert your string to datetime and will let you apply datediff without loosing the "days" part.

The above implements ODBC cannonical style for convertion. More here:

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16