SQL Server 2005 query works in SQL Server Management Studio Express but not in Delphi 2010

Question

I'm using SQL Server 2005 Management Studio Express and Delphi 2010. Fecha_hora = Date_Time is smalldatetime.

My date format is dd/mm/yyy

The dates in my table are saved like this:

08/01/2013 11:22:00 a.m.

I have this query in Delphi to know at which hours the sales are higher given a period of time; days/months, in this case I'm testing with the same day 8 Jan 2013:

  conect.Q_total_hora.Active:=false;
  conect.Q_total_hora.SQL.Clear;
  conect.Q_total_hora.SQL.Add('select datepart(hh, fecha_hora) as Hora, sum(Total) as Venta, a.tipo as Tipo');
  conect.Q_total_hora.SQL.Add('from ventas v join articulos a on v.id_articulo=a.id_articulo');
  conect.Q_total_hora.SQL.Add('where tipo='+char(39)+DBLUCB_tipo.Text+char(39)+' and cast(Convert(varchar(10), fecha_hora, 112) as datetime) between'+char(39)+DateToStr(DateTimePicker_fecha1.Date)+char(39)+ 'and'+char(39)+DateToStr(DateTimePicker_fecha2.Date)+char(39));
  conect.Q_total_hora.SQL.Add('group by datepart(hh,fecha_hora), a.tipo order by datepart(hh,fecha_hora) ');
  conect.Q_total_hora.Active:=true;

I use cast(Convert(varchar(10), fecha_hora, 112) as datetime) because I found on the internet that this way I can retrieve only the date without the time to retrieve data between dates.

In the DateTimePickers I choose 08/01/2013 as 8 Jan 2013

I used a memo to see the query memo1.Text:=conect.Q_total_hora.Text;

and the query I receive is:

select datepart(hh, fecha_hora) as Hora, sum(Total) as Venta, a.tipo as Tipo
from ventas v join articulos a on v.id_articulo=a.id_articulo
where tipo='Burrito Grande' and cast(Convert(varchar(10), fecha_hora, 112) as datetime) between'08/01/2013'and'08/01/2013'
group by datepart(hh,fecha_hora), a.tipo order by datepart(hh,fecha_hora)

The problem I have is that when I run this query in SQL Server Mgmt Studio, it returns values, but not in Delphi, and in Delphi if i set the values of the DateTimePickers to 01/08/2013 as 1 Aug 2013 it returns the values of 08/01/2012.

As far as I know (and I don't know much...) when I send a query to SQL Server it is like if I'm writing it in SQL... why if I'm sending the date 08/01/2013 as a string it doesn't return anything?

Thank you in advance. I'm not good in database, most things I look for them on the internet ^^

Answer 1

You should avoid BETWEEN for date range queries, as well as avoid any ambiguous date formats such as m/d/y or d/m/y. The query you end up with should look like this (how you get there in your delphi code I'll leave up to you):

WHERE fecha_hora >= '20130801' AND fecha_hora < '20130802'

But better yet, as the others have suggested, you should be passing the date value in via a parameter, e.g.

WHERE fecha_hora >= @date_param AND fecha_hora < DATEADD(DAY, 1, @date_param)

(If you need other cases other than single day, then use two parameters.)

For more background on date/range queries and why this is the way you should do it:

• What do BETWEEN and the devil have in common?

• Bad habits to kick : mis-handling date / range queries

And why you shouldn't use CONVERT to remove time from datetime:

• What is the most efficient way to trim time from datetime?

• Trimming time from datetime – a follow-up