Query by date with ASP and MySQL

Question

I need to make a query in a MySQL database returning records with the current date.

I found the command below and it works perfectly inside MySQL:

SELECT * FROM TBAvaliacoes WHERE DataHora = (Date_Format(Now(),'%Y-%m-%d'))

But when I do this inside ASP, it returns me the error below:

See how I am doing:

' ## LISTA RAMAIS
Set cmdListaAvaliacoes = Server.CreateObject("ADODB.Command")
cmdListaAvaliacoes.ActiveConnection = conn
cmdListaAvaliacoes.CommandText = "SELECT * FROM TBAvaliacoes WHERE DataHora = (Date_Format("&Now()&",'%Y-%m-%d'))"
response.write cmdListaAvaliacoes.CommandText
cmdListaAvaliacoes.CommandType = 1
Set rsListaAvaliacoes = Server.CreateObject("ADODB.Recordset")
rsListaAvaliacoes.Open cmdListaAvaliacoes, , 3, 3

If I enclose Now () in single quotation marks, it gives no error, but returns nothing.

Does anyone know how to get around this?

Awaiting,

Steve · Accepted Answer · 2019-08-14 17:46:03Z

1

If you want to call the Now() function defined by MySql then you shouldn't use the VB.NET function and concatenate its output to your sql. Just write the code exactly how you write it in the MySql Workbench

cmdListaAvaliacoes.CommandText = "SELECT * FROM TBAvaliacoes 
                                  WHERE DataHora = (Date_Format(Now(),'%Y-%m-%d'))"

If you want to pass a particular date then you need to format your date as expected by MySql

Dim myDate As DateTime = new DateTime(2019,8,10)

cmdListaAvaliacoes.CommandText = "SELECT * FROM TBAvaliacoes 
                                  WHERE DataHora = '" & myDate.ToString("yyyy-MM-dd") & "'"

But in this case the better approach is to use parameters (even if you have full control of what your date is)

Dim myDate As DateTime = new DateTime(2019,8,10)
cmdListaAvaliacoes.CommandText = "SELECT * FROM TBAvaliacoes 
                                  WHERE DataHora = @dat"
Dim prm = cmdListaAvaliacoes.CreateParameter("@dat", adDBDate, adParamInput)
prm.Value = myDate
cmdListaAvaliacoes.parameters.Append prm

Side note
A lot of time has passed from the last time I have used ADODB. So the parameter solution is how I remember it. Search on the net for more complete CreateParameter examples

edited Aug 14, 2019 at 17:46

answered Aug 14, 2019 at 13:59

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eder Pardeiro Over a year ago

I'm using Classic ASP. Sorry for don't say this before. Anybody can help me with this?

Steve Over a year ago

Answer updated with an example both with a formatted string as expected by MySql and with a parameter

Collectives™ on Stack Overflow

Query by date with ASP and MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related