Nrthugu

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

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

You can avoid all these issues and wrote a very much cleaner code using parameterized queries.

Try this

  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=:tipo and fecha_hora between :fecha1 and :fecha2');

  conect.Q_total_hora.SQL.Add('group by datepart(hh,fecha_hora), a.tipo order by datepart(hh,fecha_hora) ');

  conect.Q_total_hora.Prepared:=True;

  conect.Q_total_hora.ParamByName('tipo').AsString   := DBLUCB_tipo.Text;

  conect.Q_total_hora.ParamByName('fecha1').AsDateTime := DateTimePicker_fecha1.Date;

  conect.Q_total_hora.ParamByName('fecha2').AsDateTime := DateTimePicker_fecha2.Date;

  conect.Q_total_hora.Open;

Try this:

conect.Q_total_hora.SQL.Add('where tipo='+char(39)+DBLUCB_tipo.Text+char(39)+' and  fecha_hora between '+char(39)+DateToStr(DateTimePicker_fecha1.Date)+char(39)+ ' and '+char(39)+DateToStr(DateTimePicker_fecha2.Date)+char(39));

which should look like:

where tipo='Burrito Grande' and fecha_hora between '08/01/2013' and '08/01/2013'

SQL should convert the dates to datetime automagically and do the where you want.

You could also try this:

where tipo='Burrito Grande' and month(fecha_hora) = 8 and year(fecha_hora) = 2013 and day(fecha_hora) = 1

If you use the term BETWEEN '08/01/2013' and '08/01/2013' you will indeed only see records having a date value of exactly '08/01/2013'.

If the column is of type date you will get what you want, but as your column type is datetime, the contents '08/01/2013 11:22' is not between '08/01/2013 00:00:00' and '08/01/2013 00:00:00' and hence ignored.

When querying a datetime column, use a pattern like this

...

where DateTimeCol between '<StartDate>' and '<EndDate> 23:59:59,997'

This might look a bit weird, but will include all datestamps from the given date range.

Update: This is a dirty solution which only works well with data type DATETIME but will not work with SMALLDATETIME or DATETIME2.

From my point of view there are two clean solutions: Either use

where (DateTimeCol>='<StartDate>') 

  and (DateTimeCol <'<The day following the EndDate>')

or separate date and time to two columns. Of course, only if possible and no ranges smaller than a day are required (eg one hour or so).