Why is my SQL Month query not returning any results?

Question

I'm not too good with SQL so I thought I'd come here. I obviously have done research but I'm struggling to find what I've done wrong here!

So, here's my query:

SELECT HOUSE, AMOUNT
FROM housepoints 
WHERE MONTH(TIMESTAMP) = MONTH(03) + AND YEAR(TIMESTAMP) = YEAR(2020)

I'm trying to select the columns from the table of which were created in the current month, hence why I'm checking if the month and year match.

My data:

TIMESTAMP: 2020-03-14

But the statement is not returning anything! I am confused because the month and year matches but I suspect I'm just doing something wrong in the query that I don't understand.

Any ideas?

A good habit to get into is to tag your SQL related questions with the RDBMS you are using. — Jeff Holt, Mar 14 '20 at 18:39
Hi Stephen. I think it would help if you edit the question and add the table with all the fields so we can see the timestap detail. Which versio is it? https://dev.mysql.com/doc/refman/5.7/en/datetime.html. Does it work with: SELECT TIMESTAMP("2020-03-14");? — Mike, Mar 14 '20 at 18:39
If the statement parses in your chosen RDBMS, then you can learn more by using an adhoc query tool and then add the expressions `month(timestamp)` and `year(timestamp)` and `timestamp` to your select list items as well as broadening the `where` clause so that you see more examples of what happens when you translate one thing into another. — Jeff Holt, Mar 14 '20 at 18:57
If you are using MySQL the use of TIMESTAMP data type will render your application DEAD in February, 2038. The date range is from 1970 - ~ January 18, 2038. Alternative use DATETIME datatype. — Wilson Hauck, Mar 15 '20 at 01:01

forpas · Answer 1 · 2020-03-14T18:31:26.590

0

Assuming that you use MySql, you must use CURRENT_DATE as the argument in the functions YEAR() and MONTH():

SELECT HOUSE, AMOUNT 
FROM housepoints 
WHERE MONTH(TIMESTAMP) = MONTH(CURRENT_DATE) AND YEAR(TIMESTAMP) = YEAR(CURRENT_DATE)

If you are using SQL Server then replace CURRENT_DATE with GETDATE()

edited Mar 14 '20 at 18:31

answered Mar 14 '20 at 18:26

forpas

160,666
10
38
76

Caius Jard · Accepted Answer · 2020-03-14T18:46:14.050

It is better to avoid running functions on columns in a where clause, because it cripples the DB's ability to use indexes. Imagine you had 2 million rows in your table, 100 of which are from Feb 2020, and the date column is indexed. A DB can optimize a query like WHERE date BETWEEN '2020-02-01' AND '2020-02-29' to use an index and retrieve just those 100 rows it knows matter, but it can't do the same with WHERE YEAR(date) = 2020 AND MONTH(date) = 2: in this latter case it will just have to visit all 2 million rows, calling two functions per row (slooow), to find the same 100 rows

If you want data from the current month you should instead use something more like:

SELECT *
FROM table
WHERE datecolumn >= @startDate AND dateColumn < @endDate

And then get Java to work out fixed constants for the @startDate and @endDate parameters (why? because it makes the code flexible; if you code up current month into the SQL you can't later reuse the same code to query the current year)

At the very least if you're coding this logic into the SQL, only use functions for the constants to which dates are compared:

SELECT *
FROM table
WHERE 
  --add (1 minus the current day number) to get to start of month
  datecolumn >= DATEADD(day, 1-DAY(getutcdate()), CAST(getutcdate() as date)) AND
  --add 1 day to result of EOMONTH function to get constant for end of the month
  datecolumn < DATEADD(DAY, 1, EOMONTH(getutcdate()))

Note: you didn't specify which DB you use so I guessed at SQLServer and use the @ form of parameter name. It doesn't change the core message here, which is "Don't use functions on table data, in a where clause"

score 0 · Answer 3 · answered Mar 14 '20 at 18:48

Since you didn't tag DB and other answers are for SQL server and MySQL, I thought having an answer for Oracle would help as well. Looks like this is where the problem is in your query MONTH(TIMESTAMP) = MONTH(03) + AND YEAR(TIMESTAMP) = YEAR(2020). You are misusing MONTH() and YEAR() functions. Additionally there is a typo + in your query after MONTH(TIMESTAMP) = MONTH(03).

SELECT
HOUSE,
AMOUNT 
FROM housepoints 
WHERE EXTRACT(MONTH FROM TIMESTAMP) = 3 
AND EXTRACT(YEAR FROM TIMESTAMP) = 2020;

score 0 · Answer 4 · answered Mar 14 '20 at 18:53

assuming you are using mysql, you can use EXTRACT to extract the required value from the TIMESTAMP assuming it is valid

SELECT HOUSE, AMOUNT
FROM housepoints
WHERE EXTRACT(MONTH FROM TIMESTAMP) = 03 AND EXTRACT(YEAR FROM TIMESTAMP) = 2020;

for more info

https://www.w3schools.com/sql/func_mysql_extract.asp

Why is my SQL Month query not returning any results?

4 Answers4