Thursday, March 29, 2012

Combining Time with a Date

Let's say that I have two tables, one of which has a StartDate field (say,
TableA) as well as a foreign key reference to TableB which has a field
called BeginTime. Suppose that values in TableA.StartDate field have varying
dates together with a time of 00:00:00.000 (as it's a datetime field) and
TableB.BeginTime fields have varying time values (e.g. 07:00:00.000). NOTE:
TableB.BeginTime field is currently an nvarchar field.
What I want to do is compare the current date (using GETDATE()) to the
datetime value that results from combining TableA.StartDate with
TableB.BeginTime. For example, if TableA.StartDate = "03/29/2005
00:00:00.000", and TableB.BeginTime = "07:00:00.000", I want to compare the
current date to "03/29/2005 07:00:00.000". I will ultimately be trying to
determine if the difference between them is greater than a certain # of
minutes. How could I do this using SQL?Bob
Look at DATEDIFF system function.
"BobRoyAce" <bob@.decisioncritical.com> wrote in message
news:%23byP4xCNFHA.3844@.TK2MSFTNGP14.phx.gbl...
> Let's say that I have two tables, one of which has a StartDate field (say,
> TableA) as well as a foreign key reference to TableB which has a field
> called BeginTime. Suppose that values in TableA.StartDate field have
varying
> dates together with a time of 00:00:00.000 (as it's a datetime field) and
> TableB.BeginTime fields have varying time values (e.g. 07:00:00.000).
NOTE:
> TableB.BeginTime field is currently an nvarchar field.
> What I want to do is compare the current date (using GETDATE()) to the
> datetime value that results from combining TableA.StartDate with
> TableB.BeginTime. For example, if TableA.StartDate = "03/29/2005
> 00:00:00.000", and TableB.BeginTime = "07:00:00.000", I want to compare
the
> current date to "03/29/2005 07:00:00.000". I will ultimately be trying to
> determine if the difference between them is greater than a certain # of
> minutes. How could I do this using SQL?
>|||I am familiar with the DATEDIFF function, but that will not combine separate
DATE and TIME values together to give me a DATETIME. That's the piece I'm
missing here.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u1zyYHDNFHA.3076@.tk2msftngp13.phx.gbl...
> Bob
> Look at DATEDIFF system function.
>
> "BobRoyAce" <bob@.decisioncritical.com> wrote in message
> news:%23byP4xCNFHA.3844@.TK2MSFTNGP14.phx.gbl...
> varying
> NOTE:
> the
>|||Since you express datetime as a string, it is just a matter of building a st
ring expression which
can safely be converted to datetime. I didn't follow your first post, but le
ts assume that one value
is datetime and the other is a string:
DECLARE @.a datetime, @.b nvarchar(40)
SET @.a = getdate()
SET @.b = '07:00:00'
SELECT CAST(CONVERT(char(8), @.a, 112) + ' ' + @.b AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"BobRoyAce" <bob@.decisioncritical.com> wrote in message
news:%23mQ5VNDNFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am familiar with the DATEDIFF function, but that will not combine separat
e DATE and TIME values
>together to give me a DATETIME. That's the piece I'm missing here.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:u1zyYHDNFHA.3076@.tk2
msftngp13.phx.gbl...
>|||What's the 112 for?|||>> What's the 112 for?
It is the argument for CONVERT to return the ISO format( yymmdd ) for dates
represented as a string. See the topic CAST and CONVERT in SQL Server Books
Online.
Anithsqlsql

No comments:

Post a Comment