Monday, March 19, 2012

DateTime help

This simply shouldn't take all morning to figure out but for some reason it has. I simply want to insert the current date and time into a datetime field.

No matter what I try I either get errors (Syntax error converting datetime from character string.) or I get the wrong date (4/11/1900, 1/1/1900).

Here's my current SQL which gives the syntax error.


CREATE PROCEDURE [dbo].[QuoteApprovalWeb_Approve]
@.table nvarchar(50),
@.approvedby nvarchar(100),
@.quote nvarchar(50),
@.dt datetime
AS
Declare @.SQL nVarchar(4000)
Select @.SQL = 'Update [' + @.table + '] set quoteapproval = ' + "'" + @.approvedby + "', "
Select @.SQL = @.SQL + 'quoteapprovaldate = ' + @.dt + ' where quoteno ='
Select @.SQL = @.SQL + "'" + @.quote + "'"
exec (@.sql)
GO

I tried replacing @.dt with getdate() but that would always give me errors also.replace the line

Select @.SQL = @.SQL + 'quoteapprovaldate = ' + @.dt + ' where quoteno ='

with

Select @.SQL = @.SQL + 'quoteapprovaldate = ' + '''' + CAST(@.DT AS VARCHAR(20))+ '''' + ' where quoteno ='

your string would look something like
quoteapprovaldate = 'Apr 5 2004 3:35PM' where quoteno =

Navin|||Sweet - that works great!

Thanks much.

No comments:

Post a Comment