Solved

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

Posted on 2011-02-16
9
1,116 Views
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
0
Comment
Question by:sqlprg
9 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 34909654
post the code
0
 
LVL 58

Expert Comment

by:cyberkiwi
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).
0
 

Author Comment

by:sqlprg
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?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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:
http://www.experts-exchange.com/A_1499.html
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 51

Expert Comment

by:HainKurt
ID: 34909900
great post from angelIII. are you working for EE angel?
0
 
LVL 11

Expert Comment

by:SAMIR BHOGAYTA
ID: 34913549
0
 

Author Closing Comment

by:sqlprg
ID: 34916352
Grate !  Works fine,  Thanks,  appreciate it
0
 

Author Comment

by:sqlprg
ID: 34916387
Grate !  Works fine,  

Thanks angelIII,  

appreciate it
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now