MySQL Query to find rows less that a fiscal year in the form YYYY-YYYY
I have a table
table_lists like this [![enter image description here]]
1. 2018-2019 2. 2019-2020 3. 2017-2018 4. 2019-2020 5. 2018-2019 6. 2019-2020
Now I need to display results where acad_yr is less than a give acad_yr like, 2019-2020 or 2018-2019.
I tried like
select * from table_lists WHERE acad_yr < '2019-2020'
It is showing results, but don't know it is the correct and effective method.
acad_yr is of type
varchar. Any better solution for this?
Your method is fine. This comparison:
where acad_yr < '2019-2020'
is executed as a string comparison. You are using four-digit years and the strings will compare as you want them to.
MySQL select specific year or year month rows that have yyyy-mm , You could try this code. FROM_UNIXTIME(datetime_column, '%Y') AS year. or to fetch year specific record. YEAR(datetime_column)='2012'. to fetch month� 1 MySQL Query to find rows less that a fiscal year in the form YYYY-YYYY Jan 28 0 OverflowException on Integer Addition Feb 2 0 Linux environment variable Feb 21
We can use
substr() function to divide the acad_year
select * from table_list where substr(acad_yr,4) < substr('2019-2020',4);
How to Handle Calculations Related to fiscal year and quarter , SELECT DateDiff(yy,0,Getdate()). This returns 114 for this year 2014. For cases that Fiscal year starts at a different date instead of January 1,� Following is the query to find the specific row and display the record of that row − mysql> select *from DemoTable where UserId=102 and UserName='David'; This will produce the following output −
Assuming your acad_year column content is based on a fixed pattern with a separator char could try using a casting to int for the left side of the string
select * from my_table where cast(left(acad_year,4) AS UNSIGNED ) < cast(left('2020-2019',4) AS UNSIGNED )
and for fictional sample with not a fixed length but the minus sign as separator you could use.
select * from my_table where cast(left(acad_year,locate('-',acad_year)) AS UNSIGNED ) < cast(left('2020-2019',locate('-','2020-2019')) AS UNSIGNED )
MySQL Date Time - Exercises, Practice, Solution, Exercises, practice with solution of exercises on basic MySQL Date Time: exercises on Write a query to get the first day of the current year. LIMIT specifies how many rows can be returned. The form for LIMIT is. LIMIT startnumber,numberofrows. The first row that you want to retrieve is startnumber, and the number of rows to retrieve is numberofrows. If startnumber is not specified, 1 is assumed. To select only the first three customers who live in Texas, use this query:
Create and Populate Date Dimension for Data Warehouse , [Year] CHAR(4),-- Year value of Date stored in Row [YearName] CHAR(7), 2, Select DATEPART(YY , Getdate()) as YearValue, Return Value of the Year=2013 from Current Date /*Table Data type to store the day of week count for the month and year*/ Populate Fiscal Calendar fields in Dim date table. The YEAR() function returns the year part for a given date (a number from 1000 to 9999). From MySQL 4.0: More Examples. Example. Return the year part of a date
The Ultimate Guide To MySQL Date and Date Functions, In this tutorial, we will introduce you to the MySQL DATE data type and show you some MySQL uses yyyy-mm-dd format for storing a date value. In the second row, we used 80 (range 70-99) as the year, MySQL converted it to 1980. Finally, we can query data from the people table to check whether data was converted� WEEK() function. MySQL WEEK() returns the week number for a given date. The argument allows the user to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If no argument
- if oyu are sure that the entries are in the right progression based on the id you can use that instead of a varchar field to do the comparison
- What is your expected result from this query?
- This is fine, but I would simply store 2018-2019 as 2018, in a column called something like fiscal_year, or whatever was appropriate.
- @AnkitBajpai It should show rows that of acad_yr having records 2018-2019 and 2017-2018 only
- @Strawberry you are definitely right. Since the stored year spans seem to be under the form
year N - Year N + 1, this is storing useless informations. And consuming disk space. A
SMALLINT(unsigned ?) seems enough and uses 2 bytes per entries, when a
VARCHAR(9)uses at least 9 bytes per entries
- I agree, however, this will work in realistic cases. In fictionnal cases, such as video games in example, this comparison
'2020-2021' < '10000-10001'won't work
- @Strawberry any reason?
- @user7282 Functions cannot use indexes, so the OP is better off sticking with what they've got.
- How are exact results better than slow results ? Sorry, now I see OP has a good results...
- @VBoka Exact results are always better than slow results.
- @Strawberry wrong question, how are wrong results better than slow results :) hahahhaha bit I see that OP is getting what he wants, it is just the better aproach he is looking for if I got it right...
- You can do this, but why?
- The cure seems worse than the illness.
- @Strawberry why?
- @user7282 As mentioned, because functions cannot use indexes.