• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3999
  • Last Modified:

Select between today and 7 days ago

Hello there,

I saw this in a previous question
when you use
Select * from empmaster where empDate between '1/20/2003 00:00:000' and '1/29/2003 23:59:000'
to select records between two dates...

I have a table setup that has cdate setup as a coloum which stores all my dates in the format date('d/m/Y'), so I could do

$datestart = date('d/m/Y') - 7; ?
$dateend = date('d/m/Y');

Select * from empmaster where empDate between '$datestart' and '$dateend'
To show all the enteries in the last week?


0
wildzero
Asked:
wildzero
  • 4
  • 2
  • 2
  • +1
5 Solutions
 
nagkiCommented:
Assuming date1 is ur column in table containing dates..

try this:
select * from Table where date1 between date_sub(date1,INTERVAL 7 DAY) and curdate();

0
 
nagkiCommented:
small change

select * from Table where date1 between date_sub(curdate(),INTERVAL 7 DAY) and curdate();
0
 
crackyCommented:
This is a matter of preference, but I prefer to use a date expression instead of date_sub because I find it reads better in plain English:

SELECT * FROM `empmaster` WHERE `empDate` BETWEEN (CURDATE() - INTERVAL 7 DAY) AND CURDATE();

nagki's answer will give you exactly the same result though.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
lokusCommented:
Be careful with the different data type of DATE and DATETIME, curdate() should returns only the DATE portion and if you are comparing it with a DATETIME, the DATE will be extended with time 00:00:00.
In some situation, you may want to have the time be 23:59:59.
0
 
wildzeroAuthor Commented:
Hey guys
None of them are working and I think it's because the date field in the database is setup like

varchar(10) default NULL,

so it's a string field, so I would need to convert that to a date format in the select querry wouldn't I?
0
 
crackyCommented:
Is it possible to convert the column type to date? Performing date operations with a varchar field is counter-intuitive. Is it necessary for it to be of type varchar?
0
 
wildzeroAuthor Commented:
Well, all the data in the DB is currently like that, it contains MANY records at the moment so I am unsure on how it would go about converting all the records over....
Points upped
0
 
wildzeroAuthor Commented:
I have converted the column type to date and still have a problem.

I am putting the dates into the DB like
Y/m/d

and do betweens like
sdate Between Y/m/d and Y/m/d
where the first is 7 days before the following, and I get no results,
eg

3 records with sdate fields 2005/05/20 2005/05/21 2005/05/22
and if I do
sdate Between 2005/05/18 and 2005/05/26
I get no results...

any ideas?
0
 
lokusCommented:
Try
sdate Between '2005/05/18' and '2005/05/26'

2005/05/18 means a number 2005 divide by 05 divide by 18.


0
 
wildzeroAuthor Commented:
ohhhh stupid me!
I realised I made a coding error, I wrote it above fine but in my code I was doing
sdate Between 2005/05/26 and 2005/05/18

when it should be

sdate Between 2005/05/18 and 2005/05/26

:P
Thanks everyone!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now