Convert string time like '18:15' to DateTime like 23/09/2010 18:15:00

Question

How to convert VARCHAR time like '18:15' to DateTime like 23/09/2010 18:15:00. There can be any date format I just want to have date and time.

The reason is I have a column in db time of VARCHAR(5) type. I have input, time in string, from user. Which I want to compare like this

WHERE MyTable.Time < @userProvidedTime

Answer 1

I'm not sure I understand exactly you what you want but this should do the trick:

Declare @time varchar(10)

set @time = '18:15'

Select Cast(floor(cast(getdate() as float))as datetime) + @time

Answer 2

Using temporal functions:

DECLARE @userProvidedTime AS CHAR(5);
SET @userProvidedTime = '18:15';

SELECT DATEADD(DAY, 
          DATEDIFF(DAY, '1990-01-01 00:00:00.000', CURRENT_TIMESTAMP), 
          '1990-01-01 00:00:00.000'), 
+ CAST(@userProvidedTime AS DATETIME);