SQL DATE QUERY - problem (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

dupati1 -> 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 -> 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?



dupati1 -> 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



rdouglass -> RE: SQL DATE QUERY - problem (10/13/2003 15:07:20)

That's funny...[>:] I use SQL Server 2000 and here is one of my queries:

"SELECT * FROM tblItems WHERE itemStart BETWEEN '1/1/03' AND '" & Date() & "'"

It does however look like you're missing some double quotes in your strSQL line:

strSQL = SELECT * from myTable WHERE [surgeryDate] LIKE ' " & fromDate & " '

Should be:

strSQL = "SELECT * from myTable WHERE [surgeryDate] LIKE ' " & fromDate & " ' "


That any help???



Spooky -> RE: SQL DATE QUERY - problem (10/13/2003 15:09:12)

quote:


From BOL
Pattern Matching with LIKE
It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.



rdouglass -> RE: SQL DATE QUERY - problem (10/13/2003 15:16:05)

I'm still learning too....[:o][:D]

(very humbling....sorry for any misleading advice....[:(])



Spooky -> RE: SQL DATE QUERY - problem (10/13/2003 15:19:10)

Hey I only found out *ahem* recently [;)]



skywalk22 -> 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



Spooky -> RE: SQL DATE QUERY - problem (10/21/2004 15:09:37)

Ideally, SQL would prefer the date as yyyymmdd so that there is no confusion with locale.
Use a function to convert the input, or use 3 text boxes on your input form containing yyyy + mm + dd .
Then, rejoin the values when saved to the database.



skywalk22 -> 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



Spooky -> RE: SQL DATE QUERY - problem (10/21/2004 16:17:56)

At the top of the query page - try this code :

<%Session.LCID = 1033%>



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625