Question: SQL Query to select Between two days of the month

Question

SQL Query to select Between two days of the month

Answers 4
Added at 2016-12-31 09:12
Tags
Question

I want to have a SQL Query that selects between two dates of months.

Lets say between the 15th and the 15th of the next month.

This is for accounting purposes

I know with each month having different days adding 30 days or 31 days to the start date will not work especially in Feb. My accounting run is from the 15th of the month to the 15th of the next month.

My current query for selected dates are as follows

SELECT * FROM lbs_trace_etrack WHERE lbs_date  >=  '" . $dates . "' 
AND lbs_date  <= '" . $datee . "' AND lbs_client='$slcustom1' ORDER BY lbs_date DESC, lbs_time DESC

lbs_date start should be the 15th of each month and lbs_date end should be the 15th of the next month

By defining the dates as follow works but it is giving me from the November date to now and I want it from the 15th of this month seeing we have past the 15th of the current month already

$first = date('Y-m-15', strtotime("$last -1 month"));
$last = date('Y-m-t'); 

and the query

SELECT * FROM lbs_trace_etrack WHERE lbs_date  >=  '" .    
$first . "' 
AND lbs_date  <= '" . $last . "' ORDER BY lbs_date DESC

Basically as we pass the current month 15th I need it to show me the current usage from the current 15th not the previous month 15th to now

Answers to

SQL Query to select Between two days of the month

nr: #1 dodano: 2016-12-31 10:12

Could you able to use mysql day() function for your requirement.

Ex:

SELECT day("2016.12.15");
       result:
       15

Like wise you can use this function to check whether its "15"

select day(currentDate())

nr: #2 dodano: 2016-12-31 10:12

use DAY() function to get specific date

select * from My_Table where  day(str_to_date(`Date`,'%d-%m-%Y'))  = 1 or day(str_to_date(`Date`,'%d-%m-%Y'))  = 15;
nr: #3 dodano: 2016-12-31 10:12
select group_concat(year(now()),"-",month(now()),"-",15)
nr: #4 dodano: 2016-12-31 10:12

If you really want what I understand you want,

   SELECT l.* 
     FROM lbs_trace_etrack l
     JOIN (SELECT s.cd, CASE 
             WHEN s.cd >= DATE(DATE_FORMAT(s.cd,'%Y-%m-15')) 
             THEN DATE(DATE_FORMAT(s.cd,'%Y-%m-15'))
             ELSE DATE_SUB(DATE(DATE_FORMAT(s.cd,'%Y-%m-15')), INTERVAL 1 MONTH) 
               AS start_date
             FROM (SELECT CURDATE() cd) s) d
       ON (TRUE)
    WHERE l.lbs_date  >= d.start_date
      AND l.lbs_date  < d.cd  
      AND l.lbs_client = '$slcustom1' 
 ORDER BY l.lbs_date DESC, l.lbs_time DESC;

Please note that you still have the PHP variable $slcustom1 there. The JOIN with subselect is motivated by the need to call CURDATE() only once, to avoid the rare occasion in which the date might change between two subsequent invocations. Please check if comparisons <, >= etc. are what you want.

Edit: I’ve moved the computation of start_date to the joined table. Even if in this way I have to call DATE(DATE_FORMAT()) three times, it is better than to compute DATE_SUB for every row, in the case we are in the first half of the month.

Source Show
◀ Wstecz