Query with date range with a variable

Asked

Viewed 123 times

0

I’m new in SQL and I’m breaking my head to mount a query that returns the desired results.

I have a field whose date format is as follows: 2018-12-13T18:01:16.573-02:00.

I have a variable $Período the values of which are (month/year): 12/2017, 01/2018, 02/2018, etc..

This consultation worked:

SELECT *
    FROM nomedatabela
    WHERE TO_CHAR(colunadata, 'MM/YYYY') = ($PERIODO)

However, the period I want to query when selecting the variable is as follows:

  • When selecting the variable whose value is 02/2018, search the records of 06/01/2018 to 05/02/2018;
  • When selecting the variable whose value is 03/2018, search the records of 06/02/2018 to 05/03/2018;
  • and so on.

How can I do?

sql

2 answers

1


First you would need to control the initial and final dates, since you do not want to be based on the full month (from 1 to 30/31). Done this, just compare to colunadata with these two:

SELECT *
FROM nomedatabela
WHERE TO_CHAR(colunadata, '%d/%m/%Y') >= DATE_ADD(STR_TO_DATE(CONCAT('06/', $periodo), '%d/%m/%Y'), INTERVAL -1 MONTH)
  AND TO_CHAR(colunadata, '%d/%m/%Y') <= CONCAT('05/', $periodo)

Explaining better the calls what happens on DATE_ADD(STR_TO_DATE(CONCAT('06/', $periodo), '%d/%m/%Y'), INTERVAL -1 MONTH)..

  • CONCAT() -> adds day to search (05 or 06, for the beginning and end of the period);
  • STR_TO_DATE() -> transforms the string mounted (ex "06/02/2018") on a date;
  • DATE_ADD() -> adds (removes, in case) a date period;
  • INTERVAL -1 MONTH -> selects the specified range for the date_add (in the case, - a month)

  • Good afternoon colleague! First I would like to thank you for the answer. However I tried here and did not give, first the following error: pq: syntax error in or next to "$". Then I changed to SELECT * FROM databela WHERE TO_CHAR(colonnade, '%d/%m/%Y') >= DATE_ADD(STR_TO_DATE(CONCAT('06/', '$periodo'), '%d/%m/%Y'), INTERVAL -1 MONTH) AND TO_CHAR(colonnade, '%d/%m/%Y') <= CONCAT('05/', '$periodo') and gave the following error: pq: syntax error on or near "MONTH".

  • @dougvolei what your database? the example mounted was in mysql, where this mistake shouldn’t happen.

  • Good morning! My bank is Postgresql. Consegui da seguinte forma: SELECT * &#xA;FROM nomedatabela&#xA;WHERE colunadata BETWEEN TO_DATE(TO_CHAR((TO_DATE(CONCAT('06/', $PERIODO), 'DD/MM/YYYY')- INTERVAL '1 MONTH'), 'DD/MM/YYYY'), 'DD/MM/YYYY') AND TO_DATE(TO_CHAR((TO_DATE(CONCAT('05/', $PERIODO), 'DD/MM/YYYY')), 'DD/MM/YYYY'), 'DD/MM/YYYY') Thank you for your help!

0

Searching in a time interval with the command below, all records will be returned within this period.

SELECT * FROM table_name WHERE coluna_data BETWEEN 'data01' AND 'data02';

As this bringing only month/year, it may be necessary to perform a concatenation, by the example you gave it seems to me that are fixed dates of the 6th of a certain month until the 5th of next month.

It seems to me you are using PHP, I have no experience with php, so I can’t tell you how to work with the dates ai (probably you already know), but the sql that will make the query the way you explained it is this ai.

Source: https://www.w3schools.com/sql/sql_between.asp

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.