-2

I am currently developing a platform where I need 3 SQL queries to return all records between Year/Month/Week to date.

The queries have to work dynamically without hard-coded dates, please bear with me if i should split these up into 3 questions, i think they are very relatable and that's why i put all 3 in here :)

1: Year To Date

I tried this SQL query according to this question about Year To Date but I am receiving an error:

Incorrect parameter count in the call to native function 'DATEDIFF'

SELECT coins_daily_date, coins_daily_high, coins_daily_low, coins_daily_close, coins_daily_volume
FROM coins_daily
WHERE coins_daily_date BETWEEN DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0) AND GETDATE()
ORDER BY coins_daily_date ASC

2: Month To Date

I tried different things with the month function but could not figure out how to achieve/build a correct month to date SQL query.

3: Week To Date

Honestly, I have no idea where, to begin with, this one.

Works fine going back 1 year

SELECT coins_daily_date, coins_daily_high, coins_daily_low, coins_daily_close, coins_daily_volume
FROM coins_daily
WHERE coins_daily_date >= DATE_ADD(NOW(),INTERVAL -1 YEAR
ORDER BY coins_daily_date ASC

I hope someone can help or point me in the right direction :)

Best regards!

jarlh
  • 42,561
  • 8
  • 45
  • 63
ii iml0sto1
  • 1,654
  • 19
  • 37
  • Your DATEDIFF has three params. It only should have 2. https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff – complete_stranger Sep 05 '18 at 19:33

1 Answers1

2

GETDATE() is for MsSQL. Use NOW() for current datetime or curdate() for current date.

For year-to-date :

SELECT coins_daily_date, coins_daily_high, coins_daily_low, coins_daily_close, 
coins_daily_volume
FROM coins_daily
WHERE YEAR(coins_daily_date) = YEAR(NOW())
AND coins_daily_date <= NOW()
ORDER BY coins_daily_date ASC

For month-to-date :

SELECT coins_daily_date, coins_daily_high, coins_daily_low, coins_daily_close, 
coins_daily_volume
FROM coins_daily
WHERE YEAR(coins_daily_date) = YEAR(NOW())
AND MONTH(coins_daily_date) = MONTH(NOW())
AND coins_daily_date <= NOW()
ORDER BY coins_daily_date ASC

For week-to-date :

SELECT coins_daily_date, coins_daily_high, coins_daily_low, coins_daily_close, 
coins_daily_volume
FROM coins_daily
WHERE YEAR(coins_daily_date) = YEAR(NOW())
AND WEEK(coins_daily_date) = WEEK(NOW())
AND coins_daily_date <= NOW()
ORDER BY coins_daily_date ASC
DanB
  • 2,022
  • 1
  • 12
  • 24
  • Your Year To Date works perfectly, i do however get this error on the month to date qyery: 'MONTH(coins_daily_date) = MONTH(NOW()) AND coins_daily_date <= NOW() ORDER BY And this one in the Week To Date 'WEEK(coins_daily_date) = WEEK(NOW()) AND coins_daily_date <= NOW() ORDER BY coin' – ii iml0sto1 Sep 05 '18 at 19:45
  • @Daniel, you're missing an `AND` after your first condition for the MTD and WTD queries. – Zack Sep 05 '18 at 19:46
  • Sorry, I were too fast ;-) – DanB Sep 05 '18 at 19:48