Converting varchar to datetime

I have a field (column name : TimeSlot) )that contains value in HH:mm format (e.g 22:00).

Anyone know how to make a combination such as '12/26/2007' and the field i mentioned above to make it useable in datediff function?

I tried to select DateDiff(minute,'12/27/2007' + ' ' + TimeSlot, GetDate())

It gave me wrong time boundaries between these 2 date.

Pls advise.

are you trying to do the conversion within SQL Server, or in the .NET space?

if you're doing it in managed code, just convert your HH:mm string into a TimeSpan. Then convert your date string into a DateTime. Then .Add() the TimeSpan to your DateTime and you've got them combined into a new DateTime.

Seeing that you used GETDATE(), I am assuming that you are trying to do this in SQL server. Here is how to do what you want:

SELECT DATEDIFF(minute, CONVERT(smalldatetime, '12/26/2007' + ' ' + CONVERT(varchar, TimeSlot, 108)), GETDATE())
where the style 108 obtains the hh:mm:ss of your TimeSlot value.

If your TimeSlot value is stored as varchar(5), you do not have to convert it to a date before concatenation.

Ok thanks it works.

 Another question is I have a field in a table is which contains datetime, how can i query out the date (without time behind) and distinct?

If I Use

SELECT DISTINCT CONVERT(varchar, ShowTime, 3) from table

Where .............' Date comparison  here

It works totally ok but when the column contains 1st of Jan, 1st of Jan will exist although the date is not in my date comparison.

Pls help, thanks.

Perhaps you want to take a look into my blog about date time conversion

http://community.sgdotnet.org/blogs/usoup/archive/2007/10/26/Next-Datetime_2C00_-please.aspx