Solved

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

Posted on 2011-02-16
9
1,142 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 52

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 52

Expert Comment

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

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.

Question has a verified solution.

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

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

696 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