[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-02-16
9
Medium Priority
?
1,176 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 60

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 143

Accepted Solution

by:
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:
http://www.experts-exchange.com/A_1499.html
0
 
LVL 60

Expert Comment

by:HainKurt
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

649 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