|
| |
|
|
dupati1
Posts: 84
Joined: 10/6/2003
Status: offline
 |
SQL DATE QUERY - problem - 10/13/2003 13:21:20
Hi All,
I need suggestions. Below is my code snippet
<%
Dim fromDate
fromDate = "%"
If LEN(TRIM(Request.Form("txtDate1"))) > 0 Then
fromDate = "%" + Request.Form("txtDate1") + "%"
End If
strSQL = SELECT * from myTable WHERE [surgeryDate] LIKE ' " & fromDate & " '
%>
I get the following error:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting datetime from character string.
In the query "surgeryDate" is the column name in the table and is in the datetime format, and
"txtDate1" is the name of the field on the form and is in the character string format.
Can anybody suggest me how to get around this problem. I am using SQL SERVER 2000 and ASP.
Thanks in Advance
Vijay
|
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: SQL DATE QUERY - problem - 10/13/2003 13:29:04
You can't use the LIKE function using a datetime field. The '%' and 'LIKE' only work w/ text fields.
If you have a valid date, VBScript shouldn't have any problems reading the form field.
What specifically are you looking for? ...all records on a specific date or a date range?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
dupati1
Posts: 84
Joined: 10/6/2003
Status: offline
|
RE: SQL DATE QUERY - problem - 10/13/2003 13:43:14
Thanks for the reply. Ok let me say that i am looking for all the records for a date range and i write the following query:
SELECT * FROM myTable WHERE surgeryDate BETWEEN '"& fromDate &"' AND '"&toDate &"'
still i get the same conversion error.
Do i need to use any conversion functions.
Regards
Vijay
| |
|
|
|
skywalk22
Posts: 7
Joined: 10/4/2004
Status: offline
|
RE: SQL DATE QUERY - problem - 10/21/2004 14:46:59
Did dupati ever get his problem to work? I am having problems with the same issue.
I am using SQL server 2000 with FP 2003 and DRW. Here is my query:
select dispatch.dispatch,dispatch.recdate,dispatch.complete,dispatch.invoice,salesled.prod,salesled.price,salesled.tax1 from (salesled inner join sales on salesled.invoice=sales.invoice)inner join dispatch on dispatch.dispatch=sales.dispatch where Sales.invdate between '" & startdate & "' and '" & enddate & "' and salesled.prod <> 'HIST' order by dispatch.dispatch
startdate and enddate are form data that getspassed from the posting page. I know the values are getting past b/c I can do do a request.form= and get the results to post, I have even tried putting the values in a variable and putting the variable in the SQL query. Also tried cdate(variable) before putting them into the query. When I put '10/15/2004' and '10/20/2004' into the query instead of startdate and endate.....it works perfect.
Don't know what else to do. I always get this error:
Database Results Wizard Error
Description: Syntax error converting datetime from character string.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for SQL Server
| |
|
|
|
skywalk22
Posts: 7
Joined: 10/4/2004
Status: offline
|
RE: SQL DATE QUERY - problem - 10/21/2004 16:13:24
I am not trying to save anything to the database. I am simply trying to do a sql query and display the results based on 2 dates entered from a form. Like I said, it works perfect when the dates are hard coded to the sql query in this format 'month/day/year' ex. '12/06/1974'
Thanks,
Josh
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
I use SQL Server 2000 and here is one of my queries: 
k¥ 
)
