MYSQL query search between dates


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Programming MYSQL query search between dates
# 1  
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 10:17 AM.. Reason: extra stuff
# 2  
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
# 3  
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?
# 4  
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.
# 5  
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 .. Smilie 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 Smilie 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 Smilie
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #94
Difficulty: Easy
The mv command in Linux moves a file from one location to another, but only in the same directory.
True or False?

6 More Discussions You Might Find Interesting

1. Programming

Need help in mysql query

Hi All, i have a table in mysql with the following data Table name Test Assettype Serial_No Status location Mouse 123456 In Stock chennai Mouse 98765 Allocated chennai Keyboard ... (2 Replies)
Discussion started by: venkitesh
2 Replies

2. Shell Programming and Scripting

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

Hi, I've written a script to search for an Oracle ORA- error on a log file, print that line and the .trc file associated with it as well as the dateline of when I assumed the error occured. In most it is the first dateline previous to the error. Unfortunately, this is not a fool proof script.... (2 Replies)
Discussion started by: newbie_01
2 Replies

3. Programming

mysql query help

Hello i have created mysql query to compare to values and get difference in percentage as following: SELECT file_name, 100 - ((100 * (SELECT file_count FROM xipi_files z WHERE x.file_group = z.file_group AND x.file_name = z.file_name AND z.insert_date = CURDATE( ) - INTERVAL 1 DAY)) /... (1 Reply)
Discussion started by: mogabr
1 Replies

4. Shell Programming and Scripting

mysql query in shellscript

Hi, I want to run below query on shellscript but having one problm. ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME" "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and Date='$latest Date';" ) here Date column contans different below dates 2011-12-01... (11 Replies)
Discussion started by: aish11
11 Replies

5. Web Development

mysql query help

hello all i have 2 columns every column in the following format column1 2011-04-01 11:39:54 column2 2019-02-03 00:00:00 i want get difference between above data as following 2 days 11:39 how to do so ? i tried many functions but nothing works please advice what is the query... (6 Replies)
Discussion started by: mogabr
6 Replies

6. Programming

How to query one to many mysql

Hi there, I have a hierarchical database that include 4 tables. Table A is the parent of B, B is Parent of C, C is parent of D. If I want to query everything in D that is associated with A.name, how do I do that? Thanks! YanYan (0 Replies)
Discussion started by: pinkgladiator
0 Replies

Featured Tech Videos