Datetime data type conversion error ASP on IIS 7 when inserting date to SQL server 2005

Posted on 2011-02-16
Medium Priority
Last Modified: 2012-05-11
When inserting  a date to SQL server 2005 DB table datetime data type column from an ASP page running on IIS7 the following error occurs

Microsoft SQL Native Client error '80040e07'  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This error does not occur when the ASP page is running on IIS6

Can you please help to resolve the issue
Question by:sqlprg
LVL 61

Expert Comment

ID: 34909654
post the code
LVL 58

Expert Comment

ID: 34909681
There may be some regional date format handling that is different between the servers (more likely) or iis versions (less).

To be completely portable, always format the date as YYYYMMDD (no dashes, just 8 digits) or use parameters (recommended).

Author Comment

ID: 34909773
Thanks, cyberkiwi

for instance how do you insert a value that is returned by the  ASP  now() function which will have the date and time in it?
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34909848
if presume that you use now() in a string operation to build the SQL, aka like:

strSQL = "insert into yourtable ( column_name ) values ('" & now() & ") "  

Open in new window

which is incorrect coding, as you convert the date value (now) into string implicitly without specifying the format, and then in the sql you convert it from string back to datetime (presuming that that column is of data type datetime), again without specifying the format.

to fix that, you use either explicit data type conversion:
 strSQL =" insert into yourtable ( column_name ) values ( convert(datetime, '" & year(now) & "/" & month(now) & "/" & day(now) & "', 120) ) "

Open in new window

or shorter, use the database's getdate() function:
strSQL = "insert into yourtable ( column_name ) values (getdate()) "  

Open in new window

or use Command + Parameter to bind the date value... code will depend on the ASP version you use (classic or .net)
.net tutorial: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx
classic adodb example: http://www.freevbcode.com/ShowCode.Asp?ID=3687

also, my article on how to handle datetime in sql properly:
LVL 61

Expert Comment

ID: 34909900
great post from angelIII. are you working for EE angel?

Author Closing Comment

ID: 34916352
Grate !  Works fine,  Thanks,  appreciate it

Author Comment

ID: 34916387
Grate !  Works fine,  

Thanks angelIII,  

appreciate it

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question