querying a datetime field

[imosri]

Hi,

I am starting to pull out some reports out of an application
The report normally query the database for data of the current day

I stripped down the query to focus on the problem.

If I run this query on the SQL Server Query Analyzer it runs just fine.

select mt_id from m_ticket where convert(varchar,mt_fecha,101)='10/22/2004'

Now if I try to put this query on my .asp page I got an error:

<%
Dim repo__MMColParam
repo__MMColParam = "10/20/2004"
If (formatdatetime(date() ,2) <> "") Then 
  repo__MMColParam = formatdatetime(date() ,2)
End If
%>
<%
Dim repo
Dim repo_numRows

Set repo = Server.CreateObject("ADODB.Recordset")
repo.ActiveConnection = MM_color_tienda_STRING
repo.Source = "SELECT mt_fecha  FROM m_ticket  WHERE convert(varchar,mt_fecha,101)=" + Replace(repo__MMColParam, "'", "''") + ""
repo.CursorType = 0
repo.CursorLocation = 2
repo.LockType = 1
repo.Open()

repo_numRows = 0
%>

No matter what I tried I got the same error, the only way to get the query working is to put in a fixed fashion, obviously I need it to be dynamic.

The error says: Syntax error trying to convert the varchar value '09/25/2004' to a column of type int

As a note '09/25/2004' is the value of the column mt_fecha at the very first row.

Thank you in advance

Regards,

 

[Chopstik]

Usually, the first step is to see exactly what is being sent. I would response.write your SELECT statement to see exactly how it is being dynamically created and work from there. I do not believe you need the extra quotes at the end of your statement, though I also don't think they're necessarily causing your problem, either. But if your date is already in there, why are you using the Replace function?

repo.Source = "SELECT mt_fecha  FROM m_ticket  WHERE convert(varchar,mt_fecha,101)=" + Replace(repo__MMColParam, "'", "''")

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[imosri]

Chopstik,

First suggestion what results I am getting.

I query using:

select convert(varchar,mt_fecha,101) from m_ticket

I got the follwing rows:
09/25/2004
....
10/22/2004


Second suggestion: the replace function is being put by Dreamweaver MX version 7.0.1. I tried to edit it , If I do so, I will lost Dreamwaver dataset administration, this means going "solo".

 

[imosri]

Fixed!!!!

Your second suggestion makes me wonder.... varchar vs a date()?

On the query I have to have the result of date() enclosed so, the correct code is:

repo.Source = "SELECT mt_fecha  FROM m_ticket  WHERE convert(varchar,mt_fecha,101)='" + Replace(repo__MMColParam, "'", "''") + "'"

Thank you for shaking my head!!!

Reagrds

 

[Chopstik]

Glad that it worked for you!

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous