Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert a string into datetime

Posted on 2006-05-11
19
Medium Priority
?
871 Views
Last Modified: 2012-08-13
Hello Everyone,
I'm getting an error

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.


I'm thinking that error comes from this date which is a string, and which gets inserted into table1 as string, while the data type of the column is datetime...
So the solution would be to convert this data into datetime before I insert it into the table...can you help me?

CODE:

aspPostingDate=Request.Form("txtPostMonth") & "/" & Request("txtPostDay") & "/" & request("txtPostYear")

sql="INSERT INTO Table1 (postingDate)"
sql=sql & " VALUES "& aspPostingDate & ")"
0
Comment
Question by:Gemini532
  • 9
  • 4
  • 2
  • +3
19 Comments
 
LVL 12

Expert Comment

by:peterxlane
ID: 16659714
aspPostingDate=Request.Form("txtPostMonth") & "/" & Request("txtPostDay") & "/" & request("txtPostYear")
sql="INSERT INTO Table1 (postingDate)"
sql=sql & " VALUES '"& aspPostingDate & "')"

Enclosing the date in single quotes should resolve the problem
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16659724
For Access, you may have to enclose it in #'s


aspPostingDate=Request.Form("txtPostMonth") & "/" & Request("txtPostDay") & "/" & request("txtPostYear")
sql="INSERT INTO Table1 (postingDate)"
sql=sql & " VALUES #"& aspPostingDate & "#)"
0
 
LVL 5

Expert Comment

by:Willibob
ID: 16659729
Can you post the result of:

    response.write(aspPostingDate)


Also, is the year 4 digits? if not then make it 4 digits.

I've also seen this error when the Month & Day are transposed.

Bill
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Expert Comment

by:castrum
ID: 16660262
I think your code could not work without ".form" 2nd and 3rd "request" and you have get cstr values....

aspPostingDate=Request.Form("txtPostMonth") & "/" & Request("txtPostDay") & "/" & request("txtPostYear")

sql="INSERT INTO Table1 (postingDate)"
sql=sql & " VALUES "& aspPostingDate & ")"

I this will work:

aspPostingDate=cstr(Request.Form("txtPostMonth")) & "/" & cstr(Request.form("txtPostDay")) & "/" & cstr(request("txtPostYear"))

sql="INSERT INTO Table1 (postingDate)"
sql=sql & " VALUES "& aspPostingDate & ")"

let me know about result... bye
0
 
LVL 1

Expert Comment

by:castrum
ID: 16660278
I've forgot a part :)
aspPostingDate=cstr(Request.Form("txtPostMonth")) & "/" & cstr(Request.form("txtPostDay")) & "/" & cstr(request.form("txtPostYear"))

sql="INSERT INTO Table1 (postingDate)"
sql=sql & " VALUES "& aspPostingDate & ")"
0
 

Author Comment

by:Gemini532
ID: 16661026
But how can converting the data in single quotes change its datatype to datetime?
I thought single quotes was for strings?
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16661081
the data type is never changed in the ASP side of things...but SQL will know how to deal with it if it is in single quotes.
0
 

Author Comment

by:Gemini532
ID: 16661124
TEST INSERT INTO Table1 (postingDate) VALUES (5/11/2006)
0
 

Author Comment

by:Gemini532
ID: 16661131
adding the singe quotes and it didn't help...  :(
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16661208
The best test is to remove the part of code that is actually performing the insert and just do a

Response.Write sql

And then look at the SQL statement that is going to be executed.  If you can copy and paste it into Query Analyzer then it should work on your page as well.   That will also help you to determine if the date field is not being populated the way you expect.


Are you using SQL or Access or another db?

0
 

Author Comment

by:Gemini532
ID: 16661233
Maybe the error is somewhere else in the code:
Here's the entire INSERT
sql="INSERT INTO Table1 (startDate, endDate, Location, Description, postingDate, requestedBy, ccedTo, Other, EIA, EIAComments, EIALink)"
sql=sql & " VALUES ('" & dateChar1 & aspStartDate & dateChar1 & "','" & dateChar2 & aspEndDate & dateChar2 & "','" & aspLocation & "','" & aspDescription & "','" & aspEIA & "','"& aspPostingDate & "','" & aspRequestedBy & "','" & aspCCedTo & "','" & aspOther &"','" & aspEIAComments & "','" & aspEIALink & "')"
0
 

Author Comment

by:Gemini532
ID: 16661258
I'm using MS SQL, but code used to connect to MS Access, which is why I think there's problems now that I changed the database to MS SQL
0
 

Author Comment

by:Gemini532
ID: 16661341
TEST INSERT INTO Table1 (startDate, endDate, Location, Description, postingDate, requestedBy, ccedTo, Other, EIA, EIAComments, EIALink) VALUES ('5/11/2006','7/10/2006','LTest','PTest','No','5/11/2006','ah@oc.com','','','*','')
0
 

Author Comment

by:Gemini532
ID: 16661360
INSERT INTO Table1 (startDate, endDate, Location, Description, postingDate, requestedBy, ccedTo, Other, EIA, EIAComments, EIALink) VALUES ('5/11/2006','7/10/2006','LTest','PTest','No','5/11/2006','ah@oc.com','','','*','')
0
 

Author Comment

by:Gemini532
ID: 16661365
sorry the second one is OK
the word test is a mistake
0
 
LVL 1

Expert Comment

by:kbqwert
ID: 16667637
Try this:

aspPostingDate = Request("txtPostDay") & " " & MonthName(Request.Form("txtPostMonth")) & " " & Request("txtPostYear")

sql="INSERT INTO Table1 (postingDate) VALUES (CDate(" & aspPostingDate & "))"
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 16679497
To avoid the errors you are getting always use unambiguous datetime expressions, as in:

aspPostingDate = request("txtPostYear") & "-" & Request.Form("txtPostMonth") & "-" & Request("txtPostDay")

So that your end result is as follows:
INSERT INTO Table1 (startDate, endDate, Location, Description, postingDate, requestedBy, ccedTo, Other, EIA, EIAComments, EIALink) VALUES ('2006-05-11', '2006-07-11','LTest','PTest','No','2006-05-11','ah@oc.com','','','*','')
0
 

Author Comment

by:Gemini532
ID: 16703306
Will this work for datatype in the MS SQL database of datetime?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16705156
Yes.
0

Featured Post

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.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

564 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