MYSQL query search between dates

Applications Programming
1

Just a little help if possible
I have a table with date data, I want to select dates that fall between today and 1 month ago, here's my query

SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as monthago, CURDATE() as today 
FROM data_table
where (stdate between 'today' and 'monthago')

But this return nothing, also tried swapping the dates

SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as monthago, CURDATE() as today 
FROM data_table
where (stdate between 'monthago' and 'today')

This

SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as monthago, CURDATE() as today 
FROM data_table

returns the columns without an issue, what am I missing?

Also if I use:

SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as 'monthago', CURDATE() as 'today'  FROM data_table where ('stdate' between 'monthago' and 'today')

it listed the entire table regardless of the stdate

2

Notice how you're using the "columns" here in quotes?

between 'today' and 'monthago')

try doing the same thing when selecting:

select id, 'today', 'monthago', today, monthago
from (
SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as monthago, CURDATE() as today 
FROM data_table
)

ie: lose the quotes and add nested subquery ..
(if you just lose the quotes, it'll error out "column not found" )

Also, why ask this on a Unix forum? O.o

3

Thanks for the reply, I tried

select id, 'today', 'monthago', today, monthago 
from ( 
SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as monthago, CURDATE() as today  
FROM data_table )

But got:

failed : Every derived table must have its own alias

I have MYSQL running on ubuntu server :slight_smile:

---------- Post updated at 03:35 PM ---------- Previous update was at 02:30 PM ----------

So the only solution I could find was:

SELECT id, stdate  FROM data_table Where stdate > DATE_SUB(CURDATE(), INTERVAL 6 month) AND stdate < CURDATE()

This is an old version of mysql (v4.1.15) so may be the syntax with BETWEEN was incorrect for that version?

4

A nested query must have an alias, so I added 'X' as the alias in your code:

select id, 'today', 'monthago', today, monthago 
from ( 
SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as monthago, CURDATE() as today  
FROM data_table X)

As regards your earlier question, in MySQL and other databases too, there is a difference between single quote (') and backquote (`). I think you meant to use the latter, which is what Ditto was getting at.

5

I'm an Oracle guy, not very familiar with MYSQL .. :slight_smile: I was thinking his issue was the fact that:

where column = 'today'

is not the same as:

where column = today

if today is some internal function, then you probably shouldn't use single quotes ... if MYSQL uses back quotes ` .. then yeah, sure :slight_smile: but I was making an assumption MYSQL treats single or double quotes as strings.

which would be like trying to match:

where Nov 25, 2014 = "today" ..

as opposed to:
where Nov 25, 2014 = Nov 25, 2014
(ie where date_col = today ) ??

In Oracle it's:

where date_col = sysdate

but that's another story :wink: