How to Calculate Sql Where a Date Is Late
Fetching records between two date ranges
We can collect records between two date fields of a table by using BETWEEN query. We can use this to get records between two years or between two months. We can combine all this and try for getting records between two date ranges. Records of different date ranges by using DATE_SUB(), CURDATE() and BETWEEN() query. Generate Query by using Month & Year from Calendar There are more details on how to get the month part of any date field here. Generate Query by using dates from Calendar Generate Query by using date and time DEMO of how date is used to collect records Here is the code for the SQL dump of the file to create your table for testing.
Between two years
We will first start by displaying records between two years. Before that, you can read on how to get year part from a date field. Now let us try to get records between the years 2004 and 2005. Here is our query
IN the above query dt2 is our date field and in the result, both the years 2004 and 2005 will be included in our records. SELECT * FROM `dt_tb` WHERE year( dt2 ) between 2004 and 2005
Between two month ranges.
Now let us collect the records between two months ( Say Feb to Aug months without any year specification). Note that if we are using the only a month in our between command so for any year the range of month we specified will be returned by the query. For example, if we ask for records between Feb and Aug months then we will get records of between the month Feb and Aug for all the years.
Here is the example.
The above query will return us records of all the months between February and August of any year. We can specify the year also along with the months like this SELECT * FROM `dt_tb` WHERE month(dt) BETWEEN '02' and '08'
The above query may not give us the desired result as it will collect records of Feb to Aug for the years 2004 and 2005. To get the records between two months with the year we have to change this query. Let us say we want records from March 2015 to Feb 2016. SELECT * FROM `dt_tb` WHERE MONTH(dt) BETWEEN '02' and '08' AND YEAR(dt) BETWEEN 2004 and 2005
SELECT FROM table_name WHERE dte_field BETWEEN '2015-03-01' AND LAST_DAY('2016-02-01')
By using LAST_DAY function we are getting the last date of the month. Between two date ranges
Now let us move to select a range of records between two dates. Here is the SQL for this
SELECT * FROM `dt_tb` WHERE dt BETWEEN '2005-01-01' AND '2005-12-31' Date Format to use in query
You have seen we have used 'Y-m-d' date format in our query. We may not get data always in this format so here is the PHP code to convert to this format before using inside a query.
Similarly, you can change the variable $dt1 to a new date format. $date = new DateTime($dt2); $dt2=$date->format('Y-m-d');
Now we can use inside our query.
When we are offering input fields to the user to enter date then it is better to offer two calendars to the user to select dates. Here the user may like to enter one date as FROM date and expect to get all records starting from that date. The same way user may enter only TO date and expect all records equal to or before the TO date. Using these conditions we can generate Query. SELECT * FROM `dt_tb` WHERE dt BETWEEN BETWEEN '$dt1' AND '$dt2'
Once the query is generated by using the above example, we can apply it to any table and generate the records.
SQL Date References Records between two dates using DATE_SUB CREATE TABLE dt_tb ( id int(2) NOT NULL auto_increment, dt datetime NOT NULL default '0000-00-00 00:00:00', dt2 date NOT NULL default '0000-00-00', PRIMARY KEY (id) ) ; # Dumping data for table `dt_tb` INSERT INTO dt_tb VALUES (1, '2004-10-26 00:00:00', '2005-01-25'); INSERT INTO dt_tb VALUES (2, '2004-05-05 23:56:25', '2005-06-12'); INSERT INTO dt_tb VALUES (3, '2005-12-08 13:20:10', '2005-06-06'); INSERT INTO dt_tb VALUES (4, '2003-05-26 00:00:00', '2007-12-18'); INSERT INTO dt_tb VALUES (5, '2007-12-18 00:00:00', '2003-08-16');
This article is written by plus2net.com team.
▼ More on Date & Time functions in SQL
| rose | 09-06-2009 |
| I need to retrieve the records which are between the 2 days(TWO dates are of two different fields) | |
| rei | 13-07-2009 |
| I also need to get the records between dates! Is it any solution? | |
| Naveen Ram | 24-09-2009 |
| Hi, i want to select all the records which has date below 9/24/09. for eg: in datebase the records are Date 9/30/2009 9/24/2009 10/3/2009 9/26/2009 9/25/2009 in that i want to select only 9/24/09 and below. i write the query as Select Date From DateTable Where Date <= '9/24/2009'. i got result as Date 9/24/2009 10/3/2009 | |
| Girihdar | 02-10-2009 |
| hi i need the query for display the dates between the two dates, and my condition is only between dates, no retrieving the data from the database. | |
| Praise | 05-10-2009 |
| Is there a way to write this SQL code better. cos ikeep getting an error: SELECT qryViewExcesses.CIID, qryViewExcesses.CustomerName, qryViewExcesses.Industry, qryViewExcesses.RelationshipManager, qryViewExcesses.FirstDate, qryViewExcesses.DaysInExcess, qryViewExcesses.EndDate, qryViewExcesses.CCY, tbl_AllExcesses.[Date Of Report], tbl_AllExcesses.[EXCESS AMOUNT] FROM tbl_AllExcesses RIGHT JOIN qryViewExcesses ON tbl_AllExcesses.[Customer ID] = qryViewExcesses.CIID WHERE (((tbl_AllExcesses.[Date Of Report]) Between [qryViewExcesses].[FirstDate] And [qryViewExcesses].[EndDate])); | |
| Karthik | 09-10-2009 |
| hi i need the query for display the dates between the two dates, and my condition is only between dates, no retrieving the data from the database. SELECT * FROM Timesheet WHERE date BETWEEN '25/09/2009' and '07/10/2009' i didnt get any result regarding this.. pls help me to solve this problem | |
| Natalie | 03-11-2009 |
| I need to find results of a sale date that are between sysdate and 4 days from sysdate. How can I do this? | |
| smo | 04-11-2009 |
| You have to use CURDATE function, see the Must Read section at the top or visit this CURDATE | |
| Anita | 05-11-2009 |
| Hi, I want to display all the records between '2009-11-02' to '2009-11-05'. Using Between clause display dates from 2009-11-02 2009-11-03 2009-11-04 but not 2009-11-05. How can i get 2009-11-05 as well. thank you. | |
| saintjab | 24-01-2010 |
| I have two culumns date and amount. I want to find the sum between two given dates assuming table name is money. | |
| kuthey | 13-02-2010 |
| @saintjab: Dats easy. use group by | |
| vincent | 24-03-2010 |
| "Select Date From DateTable Where Date <= '9/24/2009'" use bettween.. | |
| Pattanayak | 30-03-2010 |
| Hi Anita, Please use the follwoing statement: SELECT * FROM Tablename WHERE dt BETWEEN '2009-11-02' AND '2009-11-06' thanks | |
| ankita bansal | 31-03-2010 |
| create proc SP_Get_Result ( @DateFrom nvarchar(50), @DateTo nvarchar(50) ) as SELECT * FROM Timesheet WHERE date BETWEEN @DateFrom and @DateTo i didnt get any result regarding this.. pls help me to solve this problem | |
| Big Vern | 16-04-2010 |
| I am looking to use the between dates action in Mysql, but want to use variables, so instead of SELECT * FROM Tablename WHERE date BETWEEN '2009-11-02' AND '2009-11-06' USing PHP I want to use $datefrom = date('Y-m-d',strtotime(date1)); $dateto = date('Y-m-d',strtotime(date2)); to adapt the query to SELECT * FROM Tablename WHERE date BETWEEN ".$datefrom." AND ".$dateto,"" I have tried this but it doesnt work, any ideas? | |
| Manaat | 26-04-2010 |
| i have a table in oracle where one column is xmltype and one node in the xml is date i have to write a query which selects the data from this table but between a specific date range please help | |
| kishori | 11-07-2010 |
| I have a table in mysql where one column is date and another column is medical status( FIT/Repeat ). How can I fetch the records by date and medical status. either FIT or Repeat | |
| dharma | 09-08-2010 |
| hi, i have to select the first sunday,first tuesday from date 17-8-2010 to 17-9-2010 | |
| Rik | 25-08-2010 |
| hey i am trying to use the: SELECT * FROM Tablename WHERE date BETWEEN ".$datefrom." AND ".$dateto,"" too is there any ideas to dong this ive been trying for about 2 - 3 weeks | |
| kamlesh makvana | 07-09-2010 |
| i want to display a grap of data between two date using between clause plz help me | |
| Mahesh | 14-12-2010 |
| This is Very Perfect Answer for this question | |
| Dan Lubbs | 07-11-2012 |
| ... WHERE date BETWEEN [FromDate] and [ThruDate]. Example of a FromDate is 9/1/2012 and ThruDate is 10/30/2012. I want to prompt with a normal date style not 2012-09-01. | |
| Venbha | 08-01-2013 |
| I got my need. Thank You | |
| Akhilesh | 06-08-2013 |
| I want to know the difference in the two dates. I need perfect answer using asp.net pgm sir. thank you | |
| waleed | 17-09-2013 |
| how to generate a report in php mysql. online shopping website total stock and order information . | |
| nin | 08-10-2013 |
| if i have not passed from date and to date then i want to display whole records... hows it possible | |
| Rajeev Bhatia | 30-10-2013 |
| Hi Friends, Please help me: i have a table which has a field named 'rate' and i am using between query for selecting rates and displaying records but the problem is :- how do i pass the the selected dates from two comboboxes for this i need two variables what will be the query if i have to pass variables from c# coding. | |
| Swati Umakant Hingane | 01-04-2014 |
| i have table lead and i want to search record between from_date and to_date but from_date and to_date not present in database please help me........ | |
| hello there | 20-04-2014 |
| I want to display record between fromdate - todate and also between fromtime - totime. where fromtime is of fromdate and totime is of totime. like record between 01-01-2014 08:00:00 to 03-01-2014 18:00:00. in my database both field time and date are different and from where I am taking input, is a textbox. | |
| Senthil | 28-05-2014 |
| I have a two text box for from-date and to-date, in this the to-date field has an issue that the record was not fetched from the database for that to-date search, any solution for this issue | |
| Praveen | 05-06-2014 |
| Hi, I need to pass one value of date to return the entire records of that particular month of the date. Like if the date is 05-JUN-2014 I want to see the entire records of the June month..Any idea pls post back.. Thank you | |
| jaanu | 30-06-2014 |
| Hi, i need to select the date in a database in a application the application verified time is 3days if it exceeds the compensation is 50rs per exceed day i need solution for this.plzzz post back | |
| Rahul Shitole | 02-07-2014 |
| Hi, i need generate report from date wise | |
| sandeep | 16-07-2014 |
| I want to display record between fromdate - todate and also between fromtime - totime. where fromtime is of fromdate and totime is of totime. like record between 01-01-2014 08:00:00 to 03-01-2014 18:00:00. in my database both field time and date are different and from where I am taking input, is a textbox. | |
| roopa | 12-08-2014 |
| sir i got a query as below... display order info with salesman which has given on date before 10 of any month.. will pls help me in writing a sql | |
| sam | 27-08-2014 |
| TWO TABLES ARE GIVEN ,EMPLOYEES AND DEPARTMENT . DISPLAY THE NAMES OF ALL THE EMPLOYEES WHOSE SALARY IS NOT WITHIN THE RANGE FOR THE CORRESPONDING DEPARTMENT | |
| krishna | 14-10-2014 |
| sir i would like to retrieve data from table by using from date and to date date format is (yyyy-mm-dd hh:mm:ss) please help me | |
| ahmad | 01-12-2014 |
| i am looking to get records between to dates using vb6 coding form access table, the dates are between Apr 14 to Dec 2014 help me plz | |
| raja | 08-12-2014 |
| i am looking get records between two dates using php code dates are 01-12-2014 to 08-12-2014 please help be | |
| smo | 08-12-2014 |
| SELECT * FROM dt_table WHERE date BETWEEN '2014-12-1' AND '2014-12-8' There is a link in the tutorial to generate query by using two calendars | |
| khalid | 14-02-2015 |
| awesome man.. it solved a big prob of mine.. cheers.... wish u best of luck | |
| nthiga | 28-03-2015 |
| hi people i need to select all entries from mysql db using php where checkindate is between 1800 hrs today and 6:00hrs folowing date.The issues is if a checkin was done before 1800hrs today to tommorow 0800hrs i need to pick also this for this is between 1800hrs todays anf next day 0600 hrs how can i do this thanks allot people | |
| Maloy | 25-04-2015 |
| Hi kan somone help me to select all matches between to dates. Thanks! | |
| Patrick | 27-04-2015 |
| Try unix date format (= number of seconds counting from 01-01-1970) and add 3600 (secs) for every additional hour. '27-04-2015 18:00' = 1430085600 +(18*3600) = 1430150400 to 28-04-2015 08:00 = 1430172000 + (8*3600) = 1430200800. SELECT * FROM xtable WHERE xdate BETWEEN 1430150400 AND 1430200800. You need a xdate INT(11) column with unix time in 'xtable' for this to work | |
| Swati | 05-06-2015 |
| I want to print the details from table where date_limit(column of a table) is exceeded by 7 or more days. SELECT DATEDIFF('2014-11-30','2014-11-29') AS DiffDate is working fine but i want the following to execute I want SELECT DATEDIFF('date_limit','date_limit+7') AS DiffDate | |
| Mythili | 14-07-2015 |
| preparedStatement = connection.prepareStatement("select * from empattendance where Empid='"+select[i]+"' AND Date BETWEEN '"+fromdate+"' AND '"+todate+"'"); We need to implement for multiple employees and for dynamic dates like 20-03-2015 to 30-05-2015 | |
| kowsalya | 07-04-2016 |
| How to retrieve all the records from database where date between 2014-06-01 and 2015-05-31 database contains 12 table. | |
| smo1234 | 07-04-2016 |
| You can apply between date to each table and then use Union command to get the list. First try with each table. | |
| mwaka fred | 04-05-2016 |
| How to create queries between two different years for instance between 1950 and 1960 | |
| smo1234 | 05-05-2016 |
| SELECT * FROM `dt_tb` WHERE year( dt2 ) between 1950 and 1960 | |
| Apurba | 08-06-2016 |
| Suppose I have Year and month in two different columns, then how can I retrieve value between two dates. Thanku | |
| smo1234 | 20-06-2016 |
| select * from dt_tb where year_column='2016' and month_column='Mar' | |
| shafeeq | 28-06-2016 |
| I have a table with date stored as integer format, now i want use between operator from another application which i can give only the real date, how i can write the query for this. When i gave this query EG: Date_=27091 dbo.ConvertDateDisplay(Date_) BETWEEN'13-06-2016' and. '28-06-2016' where dbo.ConvertDateDisplay(Date_)=19-01-2015 | |
| smo1234 | 29-06-2016 |
| You can't use convertdatedisplay function here . Try with strtotime to convert the date and then compare with date between. | |
| Davide | 09-03-2017 |
| Hi smo1234, I would export all DB excluding from the dump the last 6 months, how I could do it? Many thanks! | |
| smo1234 | 12-03-2017 |
| Not much idea on this , mysqldump works in SHELL command. Another way is to use copy table with your conditions ( add 6 months date ) and then take the backup. | |
| priya | 27-06-2017 |
| I want to select from-date to-date wise data show in form plz. suggest in details code in dodeigniter | |
| 11-09-2019 | |
| I want to get record every 15 days Like 1-jan-2019 t0 15-jan-2019 16-jan-2019 to 30-jan-2019 in different group etc. | |
| 17-05-2021 | |
| I want to grab data which is equal to date and between dates how can I do that? the above query works only for between dates not equal to selected dates. | |
| 21-06-2021 | |
| BETWEEN dates includes both ends , so it will work for equal to selected dates. If you don't want between the dates and only the ends to match then you can use like this. Date = '2021-03-27' OR Date ='2021-05-23' | |
How to Calculate Sql Where a Date Is Late
Source: https://www.plus2net.com/sql_tutorial/between-date.php