Pavenhimself Pavenhimself - 1 month ago 7
MySQL Question

Selecting data from database within a specified date period

I have a database with employee information and a program, which is supposed to retrieve data from this database.

The database structure seems like this:

ID | Date | signedIn | SignedOut
1 01-10-2012 08:00:00 16:00:00
1 14-10-2012 08:00:00 16:00:00
1 13-09-2012 08:00:00 16:00:00


When I then try to retrieve data between
01-10-2012
and
15-10-2012
I want go get only the first two rows. So I get a total amount of hours worked to 16.

But right now I get the total amount to 24, because it also retrieves the last one, even though it is from last month

sql sentence:

SELECT Info.UserID, Info.SurName, Info.FirstName, timetabel.Date,
FROM Info
INNER JOIN timestabel
ON Info.UserID = Bruger_tidstabel.UserID
WHERE Info.UserID = '1'
AND timetabel.Date >= '01-10-2012'
AND timetabel.Date <= '14-10-2012'"


the sentence works, so it can retrieve data from database.

Is there any way to tell the database, I am working with dates, so it will only retrieve data between two surden dates??

Answer

You can use the BETWEEN operator, it is used in a WHERE clause to select a range of data between two values.

SELECT column_name(s)
 FROM table_name
 WHERE column_name
 BETWEEN value1 AND value2

It is good idea your table field Date to be in date or time stamp format. Also, the year goes on front of the date format, like this:

SELECT Info.UserID, Info.SurName, Info.FirstName, timetabel.Date, FROM Info INNER JOIN timestabel ON Info.UserID = Bruger_tidstabel.UserID WHERE Info.UserID = '1' AND timetabel.Date BETWEEN '2012-10-01' AND '2012-10-14'
Comments