Solved

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

Posted on 2011-02-16
9
1,153 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 143

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
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34909900
great post from angelIII. are you working for EE angel?
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

691 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