CODEWITHSUNDEEP
sqlsql-server-2005
Anand Kumar M
Anand Kumar M

Reputation: 728

SQL Server datetime conversion Problem

I need to insert a datetime value into datetime column in SQL Server 2005

I am passing DateTime variable from the .aspx page

Which needs to be inserted in the column.

Example:

Table(date datetime)

@sql = 'insert into Table (date) values('+@datetime+')'
exec(@sql)

getting conversion failed message.

Any idea how to do this.

Very Urgent.

Thanks.

Upvotes: 0

Views: 651

Answers (6)

Yogesh Bhadauirya
Yogesh Bhadauirya

Reputation: 1235

Declare @datetime datetime
Set @datetime=GetDate()

Declare @sql nvarchar(1000)
Declare @param nvarchar(1000)
Set @param='@datetime datetime'

SET @sql = 'insert into Table (date) values(@datetime)'

exec sp_executesql @sql,@param,@datetime

you have to learn the sql injection for dynamic queries.

Upvotes: 0

jklDev
jklDev

Reputation: 11

Try making this small change to your sql statement

@sql = "insert into Table (date) values ('"+ @datetime + "')"

Upvotes: 0

Ta01
Ta01

Reputation: 31610

Besides the other suggestions with delimiters and parenthesis mismatches, Date is a reserved keyword, use [Date] instead.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

Make sure your query/stored procedure are expecting to receive the parameter as a datetime variable (not varchar(20), or anything else like that), and make sure your ASP.Net code is passing the value as a datetime value also.

Basically, for best datetime handling, convert them from strings into datetimes as early as possible when accepting them as input (e.g. convert them in an appropriate event in your code behind in ASP.NET), keep them as datetimes whenever passing them to/from the database, and fromat them back as strings as late as possible when outputting them (e.g. in view code for asp.net mvc, or when assigning to the Text property of an ASP.Net control)

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You need string delimiters - in T-SQL this is doubled-up single quotes.

SET @sql = 'insert into Table (date) values('''+@datetime+''')'
exec(@sql)

However it still might fail, depending on the format of the string. Any reason you're building a SQL string, prone to SQL injection, instead of just saying:

INSERT Table(date) SELECT @datetime;

?

Upvotes: 1

JNK
JNK

Reputation: 65147

Add escaped single quotes around the datetime value. Normally they are passed as strings.

See if this works:

@sql = 'insert into Table (date) values('''+@datetime+''')'
exec(@sql)

Upvotes: 1

Related Questions