Convert Date For Comparison

Question

Post reply

Convert Date For Comparison

David-155102

SSCarpal Tunnel

Points: 4361
More actions
December 13, 2004 at 7:59 am

#60616

I have a table called Start_Times_T that has a field called Time_VC, Time_VC is a varchar field that holds times i.e. "09:00:00".
What I want to do is take the current day and add the value contained within the Time_VC field to it. This would then produce something like 13/12/2004 09:00:00. Once I have this value I want to convert it to DATETIME type so that I can use DATEDIFF to compare this new value against the value returned by GETDATE(). The result would be the difference between 13/12/2004 09:00:00 and GETDATE().
Can anyone help me achieve this please?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rajiv-170183 Ten Centuries Points: 1167 More actions · Answer 1

hey david

why not try the "cast" or "convert" methods

regards

Rajiv.

Yoda-193403 SSC Eights! Points: 988 More actions · Answer 2

declare @time varchar(10);

set @time= '09:00:00'

select convert(Datetime, convert(varchar(10), getdate(),101) + ' ' + @time)

MikeMcDonald SSChasing Mays Points: 643 More actions · Answer 3

Something like this ...

select datediff(minute,convert(datetime,'09:00:00'),convert(datetime,convert(char(8),getdate(),8)))

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

More than one way to skin that cat:

declare @time varchar(10);

set @time= '09:00:00'

SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0)+CAST(@time AS DATETIME)

SELECT CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112))+CAST(@time AS DATETIME)

SELECT CONVERT(CHAR(8),GETDATE(),112)+CAST(@time AS DATETIME)

SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)+CAST(@time AS DATETIME)

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) +CAST(@time AS DATETIME)

SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME)+CAST(@time AS DATETIME)

SELECT CAST(CAST(SUBSTRING(CAST(GETDATE() AS binary(8)),1,4) AS INT) AS DATETIME)+CAST(@time AS DATETIME)

should all work fine

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 5

Thanks everyone, I won't be able to check the suggestions right now because I don't have access to SQL Server today but there are enough suggestions there to ensure that at least one will work! Thanks again!