INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

INSERT statement - Conversion failed when converting date

INSERT statement - Conversion failed when converting date

(OP)
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')  

RE: INSERT statement - Conversion failed when converting date

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:


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


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

RE: INSERT statement - Conversion failed when converting date

(OP)
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')  

RE: INSERT statement - Conversion failed when converting date

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

RE: INSERT statement - Conversion failed when converting date

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

RE: INSERT statement - Conversion failed when converting date

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....

CODE

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

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

But this query works...

CODE

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 varCreateDate = date()

with

CODE

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

RE: INSERT statement - Conversion failed when converting date

(OP)
Worked like a charm - many thanks!!!

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

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close