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

Combining Pass-Through Queries into a Stored Proc.

Is it possible to combine multiple Views into a Stored Procedure? Can I reference the results from one (1) View within the stored procedure? If so, how would I go about it?
Thanks!!Refer to Books online for Using Pass-Through Queries as Tables topic.

column reference error

Hi,
Can anyone help me with what I should be looking for with this error?
(I have also tried putting single quotes around the search number in the
query - with the same results)
Thanks!
Rich.
An unhandled exception of type 'System.Exception' occurred in
receivingdb.dll
Additional information: Error number = 547
Error class = 16
Error state = 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_purchaseorders_receivinglog'. The conflict occurred in database
'Receiving', table 'purchaseorders', column 'delivery'.
DELETE FROM [receivinglog] WHERE [delivery] = 125Rich K wrote:
> Hi,
> Can anyone help me with what I should be looking for with this error?
> (I have also tried putting single quotes around the search number in
> the query - with the same results)
> Thanks!
> Rich.
>
> An unhandled exception of type 'System.Exception' occurred in
> receivingdb.dll
> Additional information: Error number = 547
> Error class = 16
> Error state = 1
> DELETE statement conflicted with COLUMN REFERENCE constraint
> 'FK_purchaseorders_receivinglog'. The conflict occurred in database
> 'Receiving', table 'purchaseorders', column 'delivery'.
> DELETE FROM [receivinglog] WHERE [delivery] = 125
You have a foreign key column in another table. You need to delete the rows
in that table that are controlled by the rwo you are deleting in this table.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OgZIYd47FHA.3976@.TK2MSFTNGP15.phx.gbl...

> You have a foreign key column in another table. You need to delete the
> rows in that table that are controlled by the rwo you are deleting in this
> table.
>
As easy as that eh?
Thanks Bob it makes sense now!

column reference as parameter of sp

Is it possible to have a column reference as the parameter of a stored
procedure? The books say parameters have to be literal expressions, so
I'm guessing it's not possible, but I thought I'd ask anyway.
I want to do something like this:
INSERT INTO Table1 ([Column1]) (SELECT ? FROM Table2)
The single, unnamed input parameter needs to be the name of one of the
columns in Table2, such as [Column2]. I want it to be a stored
procedure because the query must run many times with alternating column
references and literals.
ShaileshEither
exec a string, or look at using sp_executesql... either will work... both
are doc'd in books on line.
"Shailesh Humbad" <humbads1@.hotmail.com> wrote in message
news:FrmYa.246417$BA.59826426@.twister.columbus.rr.com...
> Is it possible to have a column reference as the parameter of a stored
> procedure? The books say parameters have to be literal expressions, so
> I'm guessing it's not possible, but I thought I'd ask anyway.
> I want to do something like this:
> INSERT INTO Table1 ([Column1]) (SELECT ? FROM Table2)
> The single, unnamed input parameter needs to be the name of one of the
> columns in Table2, such as [Column2]. I want it to be a stored
> procedure because the query must run many times with alternating column
> references and literals.
> Shailesh
>

Column name not recognised even though the procedure returns a table with that name?

I am databinding a dataset formed from a stored procedure,(all done on page_load).

However, the reference to the column name is not recognised with the following error being returned.

Column with name "CalcVal" was not found.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.ArgumentException: Column with name "CalcVal" was not found.

Source Error:

Line 152: chartCommand.Fill(chartDs);Line 153: Chart1.DataSource = chartDs;Line 154: Chart1.DataBind();Line 155:Line 156: }

This worked before deployment and the other controls which are bound to data using stored procedures seem to work. However, these other data sources are all sqlDatasoure controls.

Any suggestions would be helpful.

I have found issues relating the to user ID and password which seems to depend on whether the web server and data server are the same machine or whether they are remote from each other.

cheers-jim.

The database folks left debug on which was returning an additionaltable that didn't have the column being referenced!!!

All solved. Cheers-jim