I tried to convert a varchar variable stored in my database as HH:MM:SS to an actual datetime format HH:MM:SS. I did get the value for HH:MM:SS but the attempt also prefixed the YYYY:MM:DD along with the expected result.

Following is the code that I used to convert this varchar value to HH:MM:SS and the result I got.

Code I tried :

DECLARE @Duration Varchar(10)
SET @Duration = '00:01:23'

SELECT CONVERT(datetime, Duration, 8) AS duration

The output I got :

1900-01-01 00:01:23.000

The expected output:

00:01:23

Please let me know what needs to be changed in this. Thank you!

CodePudding user response：

If you want time, why are you converting to datetime? Given the name it shouldn't be surprising you get both date and time. Try:

DECLARE @Duration char(8) = '00:01:23';
SELECT duration = CONVERT(time(0), @Duration);

Results:

• Example db<>fiddle

Just keep in mind that time (nor any date/time type) is not meant to represent a duration or interval. Because what happens when your duration or interval exceeds 24 hours?

CodePudding user response：

What you posted is a time, not a date or datetime. A duration isn't a date. The date types are binary, they don't have prefixes.

You can define a time directly with :

Declare @Duration time ='00:01:23'

Or you can cast a string to a time:

Declare @Duration varchar(10)
Set @Duration = '00:01:23'
Select cast(@Duration as time)  as duration

or

Declare @Duration varchar(10)
Set @Duration = '00:01:23'
Select convert(time, @Duration,8)  as duration

Unfortunately that's not a duration, it's a time of day. It can only store values between 00:00 and 23:59:59.9999999.

SQL Server has no interval/duration type.

CodePudding user response：

Declare @Duration Varchar(10) 
Set @Duration = '00:01:23'
select convert(time,@duration,8);