Get data based on year and month

mysql select where month and year
how to get last 3 months data in mysql
sql group by month and year from timestamp
where clause to find all records in a specific month and year
how to get month and year from date in mysql
how to get last 3 months data in sql
how to get records based on date in sql
how to fetch data date wise from database in php

I have a table with some data : month_number (int), year(int).

I want to get data based on 2 years and 2 months

SELECT
  tt.year AS raw_annee,
  tt.month AS raw_mois,
  tt.product AS raw_produit,
  tt.quantity_product AS tonnes_prod,
  tt.quantity_n AS raw_tonnes_N,
  tt.quantity_p AS raw_tonnes_P,
  tt.quantity_k AS raw_tonnes_K
FROM test_test as tt    
  WHERE (tt.year >= 2014 AND tt.month_number >= 5)
  AND (tt.year <= 2018 AND tt.month_number <= 5)
  ORDER BY tt.year desc, tt.month_number desc;

But I get only data for month 'may' from 2014 to 2018, but the idea is to get data from may 2014 ---> may 2018

Can you help me please ?

You only want to compare months for first and last years:

  WHERE (tt.year > 2014 AND tt.year < 2018)
    or  (tt.year = 2014 AND tt.month_number >= 5)
    or  (tt.year = 2018 AND tt.month_number <= 5)

Alternatively:

WHERE (tt.year * 100 + tt.month_number >= 201405)
  AND (tt.year * 100 + tt.month_number <= 201805)

Or, not supported by MySQL:

WHERE (tt.year, tt.month_number) between (2014,5) and (2018,5)

Adapted to MySQL by Raymond Nijland:

WHERE (tt.year, tt.month_number) >= (2014,5) AND (tt.year, tt.month_number) <= (2018,5)

Excel formula: Get first entry by month and year, To retrieve a date associated with a last entry tabular data, you can use a a formula based on the LOOKUP function. In the example shown the formula in H5 is: =� As you can see, the Year and Month has been stored in a YYYYMM format in an alias called “year_month_registered.” Using DATE_FORMAT to get the year and month. Another alternative is to use the DATE_FORMAT function. This can be particularly useful if you are looking for a specific format. For example, if you want a hyphen to separate the year and the month, then you can use the following:

I think what you need is the following:

SELECT
  tt.year AS raw_annee,
  tt.month AS raw_mois,
  tt.product AS raw_produit,
  tt.quantity_product AS tonnes_prod,
  tt.quantity_n AS raw_tonnes_N,
  tt.quantity_p AS raw_tonnes_P,
  tt.quantity_k AS raw_tonnes_K
FROM test_test as tt    
  WHERE (tt.year > 2014 AND tt.year < 2018) OR  (tt.year = 2014 AND tt.month_number >= 5) OR  (tt.year = 2018 AND tt.month_number <= 5)
  ORDER BY tt.year desc, tt.month_number desc;

Just an edit in your logic in your where statement

Selecting records between two date range query, Query for Getting records between two years, month or date ranges from a mysql table. SELECT * FROM `dt_tb` WHERE year( dt2 ) between 2004 and 2005 We may not get data always in this format so here is the PHP code to convert to this format Generating query based on user inputs of date from two calendars Extract only month and year from the date with formulas. The following simple formulas may help you to extract only the month and year from the given date, please do as follows: 1. Enter the formula: =TEXT(A2,"mmm-yyyy") into a blank cell besides your data, C2, for instance, see screenshot: 2. Then drag the fill handle down to the cells that

Other way with building date :

STR_TO_DATE(CONCAT(tt.year,'-',tt.month_number,'-',01), '%Y-%m-%d') 

Example :

WHERE STR_TO_DATE(CONCAT(tt.year,'-',tt.month_number,'-',01), '%Y-%m-%d')  >= '01-05-2014' AND  STR_TO_DATE(CONCAT(tt.year,'-',tt.month_number,'-',01), '%Y-%m-%d') <= '01-05-2018'

Create date from day, month, year fields in MySQL

How can we extract the Year and Month from a date in MySQL?, For extracting YEAR and MONTH collectively then we can use the To understand it, consider the following function using the data from table� It would be better to convert your target year and month to match the data type of the column, and then use a range: SELECT * FROM sales WHERE sellDate >= date '2017-03-01' AND sellDate < date '2017-04-01'. Or if the year and month are variables (so you can't use a date literal) you can use to_date () to convert the year and month to a date, and add_months () to find the start of the following month, e.g.:

You can always do the comparison as a date:

where date(concat_ws('-', tt.year, tt.month_number)) between '2014-05-01' and '2018-05-01

If you need to do date comparisons, then I would recommend that you store the values as dates.

Do a select in MySQL based only on month and year?, To select MySQL based on month and year, use in-built function YEAR() and MONTH(). The syntax is as follows −select *from yourTableName� If Date is grouped by more than one component (i.e. year and month) field names will appear differently in the pivot table field list. The important thing is to group by year and use that grouping as the base field. As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the year. Then add the Year field to the pivot table directly.

Extract month and year to a new column in Pandas, A step-by-step Python code example that shows how to extract month and year from a date column and put the values into new columns in Pandas. Provided by � To add additional date columns like Month, Quarter, and Year, a date column must have one row for every day for each year included in the date table. For example, if your dataset has dates ranging between February 1st, 2015 through to October 30th, 2015, and you report on a calendar year, you will need a date table with at least a date range

MySQL MONTH() function, MySQL MONTH() returns the MONTH for the date within a range of 1 to 12 <div class="col-md-12"> <h2>Extract MONTH from 2009-05-18:</h2> createStatement(); String Data ="SELECT MONTH('2009-05-18')"; Publishers those who have established be after seventh MONTH of the year, i.e. July, are� A step-by-step Python code example that shows how to extract month and year from a date column and put the values into new columns in Pandas. Provided by Data Interview Questions, a mailing list for coding and data interview problems.

MONTH function (DAX), MONTH. 07/08/2020; 2 minutes to read Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for� To do that, you can create a new calculated column in your date table (if it is already not there) which is the concatenation of your year and month. Then, if you place that in the axis, you will get 36 months when you select 3 months.

Comments
  • Which dbms. MySQL or MS SQL Server?
  • @jarlh MySQL....
  • fast typing mate.
  • The last filter will result in a error like you expected. i wanted to try this because i never saw the syntax used before in (My)SQL,
  • @RaymondNijland, I removed the WHERE clause and found your query correct!
  • well i removed the comment because i was thinking WHERE (tt.year, tt.month) >= (2014,5) AND (tt.year, tt.month) <= (2018,5) was generating invalid results..Well it indeed didn't and i didn't validate the results before, i think i need caffeine badly..