Home > Net >  Convert varchar 'hh:mm:ss' to datetime hh:mm:ss in SQL Server
Convert varchar 'hh:mm:ss' to datetime hh:mm:ss in SQL Server

Time:10-09

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:

duration
00:01:23

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);
  • Related