MYSQL query search between dates

11-25-2014

Registered User

87, 2

Join Date: Dec 2010

Last Activity: 10 May 2017, 10:08 AM EDT

Location: Barry, UK

Posts: 87

Thanks Given: 11

Thanked 2 Times in 2 Posts

MYSQL query search between dates

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

Code:

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

Code:

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

This

Code:

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:

Code:

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

Last edited by barrydocks; 11-25-2014 at 09:17 AM.. Reason: extra stuff

barrydocks

View Public Profile for barrydocks

Find all posts by barrydocks

11-25-2014

Registered User

70, 10

Join Date: Mar 2014

Last Activity: 30 September 2015, 11:58 AM EDT

Location: Toronto, Canada

Posts: 70

Thanks Given: 5

Thanked 10 Times in 10 Posts

Quote:

Originally Posted by barrydocks

Just a little help if possible

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

Code:

between 'today' and 'monthago')

try doing the same thing when selecting:

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
)

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

Ditto

View Public Profile for Ditto

Find all posts by Ditto

11-25-2014

Registered User

87, 2

Join Date: Dec 2010

Last Activity: 10 May 2017, 10:08 AM EDT

Location: Barry, UK

Posts: 87

Thanks Given: 11

Thanked 2 Times in 2 Posts

Thanks for the reply, I tried

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 )

But got:

Code:

failed : Every derived table must have its own alias

Quote:

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

I have MYSQL running on ubuntu server :-)

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

So the only solution I could find was:

Code:

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?

barrydocks

View Public Profile for barrydocks

Find all posts by barrydocks

11-25-2014

Registered User

846, 29

Join Date: Jan 2007

Last Activity: 2 December 2019, 5:59 PM EST

Posts: 846

Thanks Given: 94

Thanked 29 Times in 25 Posts

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

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.

figaro

View Public Profile for figaro

Find all posts by figaro

11-25-2014

Registered User

70, 10

Join Date: Mar 2014

Last Activity: 30 September 2015, 11:58 AM EDT

Location: Toronto, Canada

Posts: 70

Thanks Given: 5

Thanked 10 Times in 10 Posts

Quote:

Originally Posted by figaro

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

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.

I'm an Oracle guy, not very familiar with MYSQL ..

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

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

Ditto

View Public Profile for Ditto

Find all posts by Ditto

Programming

MYSQL query search between dates

6 More Discussions You Might Find Interesting

1. Programming

Need help in mysql query

Discussion started by: venkitesh

2. Shell Programming and Scripting

Search pattern on logfile and search for day/dates and skip duplicate lines if any

Discussion started by: newbie_01

3. Programming

mysql query help

Discussion started by: mogabr

4. Shell Programming and Scripting

mysql query in shellscript

Discussion started by: aish11

5. Web Development

mysql query help

Discussion started by: mogabr

6. Programming

How to query one to many mysql

Discussion started by: pinkgladiator