INSERT statement - Conversion failed when converting date

[jasonhuibers]

SQL Server 2008 Express
CreateDate Datetime datatype

When I execute the query below I get the error:
-Conversion failed when converting date and/or time from character string.

varCreateDate = date()
varCreatedBy = "jsmith"

sql_insert = "insert into Table1(CreateDate,CreatedBy) values ('" & varCreateDate & "', '" & varCreatedBy & "')"

response.write sql_insert

This is the output:

insert into Table1 (CreateDate,CreatedBy) values ('19/03/2012 5:41:20 PM', 'jsmith')

 

[gmmastros]

You should read this blog I wrote a couple years ago to understand why you are getting this error.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/setting-a-standard-dateformat-for-sql-se

Date formats are controlled by the login language of the connection made to the database (in the connection string). If the language is set to us_english (for example), SQL Server will interpret your date at month=19, day = 3.

You should format your date yyyymmdd hh:mm:ss AMPM so that your SQL looks like this:

[tt]
insert into Table1 (CreateDate,CreatedBy) values ('20120319 5:41:20 PM', 'jsmith')
[/tt]

Note that there are no spaces, dashes, slashes, or anything between the year, month and day. SQL Server will always interpret 8 numbers as Year-Month-Day.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jasonhuibers]

varCreateDate = now()
varCreateDate = replace(varCreateDate,"/","")

sql_insert = "insert into Table1 (CreateDate,CreatedBy) values ('" & varCreateDate & "', '" & varCreatedBy & "')"

Output:

insert into Table1 (CreateDate,CreatedBy) values ('19032012 7:02:56 PM', 'jsmith')

 

[gmmastros]

Nope. It's more than just removing the slashes. You need to make sure the year come first, then the month, and finally the day.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jasonhuibers]

Still ecnounter the same error in response to previous comment:
Conversion failed when converting date and/or time from character string.

 

[xwb]

There are two date formats depending on whether you are using ODBC or OLEDB. To work this out, you need to look at your connection statement. Some providers are OLEDB, others are ODBC.

For ODBC,

http://msdn.microsoft.com/en-us/library/ms190234(v=sql.90).aspx

In your case { ts '2012-03-19 19:02:56' }

For OLEDB,

http://msdn.microsoft.com/en-us/library/bb677303.aspx

In your case '2012-03-19 19:02:56'

 

[gmmastros]

xwb, your OLEDB example is not 100% safe. Specifically, there are certain languages that will cause your format to be mis-interpreted by SQL Server.

In a query window....

set language british
Select Month('2012-03-19 19:02:56')

The above query will report an error. [tt][red]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.[/red][/tt]

But this query works...

set language british
Select Month('20120319 19:02:56')

In fact, you could change this to any language and it will always, 100% work.

Jason

replace [tt]varCreateDate = date()[/tt]

with

varCreateDate = cStr(Year(date()) * 10000 + Month(date()) * 100 + Day(date())) & " " & time()

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jasonhuibers]

Worked like a charm - many thanks!!!

varCreateDate = cStr(Year(date()) * 10000 + Month(date()) * 100 + Day(date())) & " " & time()