SQL dateadd and datediff

Question

I'm trying to write a SQL statement where it will return the number of record per day.

This my table

id |      ip       |       date
----------------------------------------
1   136.196.41.91   2017-01-01 11:08:14
2   28.158.146.198  2017-01-01 11:08:15
3   209.140.42.236  2017-01-01 11:08:16
4   241.184.227.171 2017-01-02 11:08:17
5   6.183.99.169    2017-01-02 11:08:18
6   105.18.254.15   2017-01-03 11:08:19

expected result something similar to:

2017-01-01: 3
2017-01-02: 2
2017-01-03: 1

I m trying this

select date, count(id)
from demo
WHERE date >=dateadd(day,datediff(day,0,GetDate())-7,0)
GROUP BY date

But for some reason i'm getting this error in "phpmyadmin"

Error

SQL query: Documentation

select date, count(id)
from demo
WHERE date >=dateadd(day,datediff(day,0,GetDate())-7,0)
GROUP BY date LIMIT 0, 25

MySQL said: Documentation
#1582 - Incorrect parameter count in the call to native function 'datediff'

I can't find what I'm doing wrong.

And i thought it might be from the date format, so i changed it into date only but with no luck.

Please advise.

According to the documentation for Mysql [DATEDIFF](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff) only takes two arguments while you are passing 3 arguments , Possible duplicate of [Incorrect parameter count in the call to native function 'DATEDIFF'](http://stackoverflow.com/questions/23250555/incorrect-parameter-count-in-the-call-to-native-function-datediff) — hassan, Mar 07 '17 at 07:35
What you trying in `WHERE` clause? What is your goal? according to your example and expected result, you just need simple grouping by DATE(`date`) — Oto Shavadze, Mar 07 '17 at 07:40
@OtoShavadze my database contains the full date, meaning that if i grouped the table by date, it want be grouped if in the same day i have for exemple: 2017-01-01 11:08:14 && 2017-01-01 11:08:15. — Joey, Mar 07 '17 at 08:00
@HassanAhmed, yes i can see that it takes 2 parameters, but everywhere i search for solution, they are always adding 3 parameters and i can't find out why. Even in the below answers they are adding 3 parameters — Joey, Mar 07 '17 at 08:06

score 1 · Answer 1 · answered Mar 07 '17 at 07:37

1

In MySQL the DATEDIFF function only accepts 2 parameters:

DATEDIFF(date1, date2)

will return the difference betwee date1 and date 2 like date1-date2.

See w3schools.com/sql/func_datediff_mysql.asp

answered Mar 07 '17 at 07:37

waka

3,362
9
35
54

yes i can see that it takes 2 parameters, but everywhere i search for solution, they are always adding 3 parameters and i can't find out why. Even in the below answers they are adding 3 parameters. – Joey Mar 07 '17 at 08:03
@Joey It depends on the SQL-Language you are using. `DATEDIFF` in `TSQL` expects 3 arguments, in `MySQL` only 2 arguments. – waka Mar 07 '17 at 08:06
when removing one of the parameters, a new error is being generated: #1305 - FUNCTION test.dateadd does not exist – Joey Mar 07 '17 at 08:10
@Joey In MySQL it's `DATE_ADD()` with an underscore. – waka Mar 07 '17 at 08:15

score 1 · Answer 2 · answered Mar 07 '17 at 08:15

1

Check if this help,

select date(date), count(*) from demo group by date(date);

answered Mar 07 '17 at 08:15

ProgrammerBoy

876
6
19

Thank you, this is what i was looking for. – Joey Mar 07 '17 at 08:45

score 1 · Accepted Answer · answered Mar 07 '17 at 08:31

1

Try this

SELECT DATE(`date`), count(*) FROM demo
GROUP BY DATE(`date`)
-- here your ordering criteria
LIMIT 0, 25

answered Mar 07 '17 at 08:31

Oto Shavadze

40,603
55
152
236

Thank you, this is what i was searching for. – Joey Mar 07 '17 at 08:44
Can you tell m sir how to use below query in MySQL .....DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -3 Month), INTERVAL 0 day,DATEDIFF(CURDATE(),SysUTCDATETIME())) – Kapil Soni Apr 28 '21 at 18:02
@Kapilsoni - Probably could better if you ask new question about that – Oto Shavadze Apr 28 '21 at 18:06
It possible via comment sir? – Kapil Soni Apr 28 '21 at 18:13

score 0 · Answer 4 · answered Mar 07 '17 at 07:36

0

Use GROUP BY Clause only :

SELECT date, count(id)
FROM demo
GROUP BY date

you need WHERE condition means : 

SELECT date, count(id)
FROM demo
WHERE DATEDIFF(your_datecolumn,GETDATE()) <= 0
GROUP BY date

answered Mar 07 '17 at 07:36

Mansoor

4,061
1
17
27

the first one without "Where" gave me what i need, but this is only if the date field is formated as "date" and not "datetime". I prefer to solve it with the type "datetime". the second one, with the where condition, gave me the same error as before. – Joey Mar 07 '17 at 08:05

SQL dateadd and datediff

4 Answers4