I have a database in which the packages of some clients and the date of creation of said package are stored.
I need to get how many packages a client makes every day in a month. I have tried with
SELECT distinct fecha_creacion FROM paqueteria.paquetes
where remitente=73
and date(fecha_creacion) >= '2017-10-01'
and date(fecha_creacion) <='2017-10-30'
The problem is that date_creation is in a datetime format YYYY-MM-DD HH-MM-SS and from the same day I get several records but with different times
2017-10-02 13:57:34
2017-10-02 13:58:15
2017-10-03 14:58:32
What you need is to make use of the function
DATE_FORMAT
:Or the function
DATE
:You can check its operation in this version online ( with
DATE_FORMAT
and withDATE
).In both cases I have used the alias
fecha_creacion
so that the name of the field remains the same even though its content is only year, month and day.If you want it to count the number of packages then you should use
GROUP BY
instead ofDISTINCT
:If you want to avoid the
DATE(fecha_creacion)
then you should change the alias to avoid confusion with the field name (which takes precedence):Note that the query is not optimized because they are neither indexes
fecha
norDATE(fecha_creacion)
.I would do it simply like this:
proof of concept
VER DEMO
Result:
To show you the number of packages, I would execute the following query:
I hope it helps you. Cheers
Friend converts the date with Cast like so:
I did not try it, I wrote it and nothing else.