Get data by month and date from database

Question

0.00/5 (No votes)

See more:

HI,

I have a fix date table with from_date and to_date, I want to get data
between two dates via

%m %d

and my search date format is mm-dd. I have tried many thing but no solution.

This is my query

"select id from monthly_tool where "from_date" > 12-22 and "to_date" < 01-09 "

but it is showing me 0 result

my table structure is %m %d

from_date	to_date	
12-21 	   01-19 	
01-20 	   02-18
....

How can i search from a table with %m %d between two column

Thanks

What I have tried:

I also tried to change column date format to %m %d. but no solution

Posted 5-Mar-20 20:36pm

Hello Infobekcons

Updated 5-Mar-20 22:24pm

v2

Add a Solution

Comments

Jörgen Andersson 6-Mar-20 3:22am

What database is it?

Hello Infobekcons 6-Mar-20 3:46am

mysql

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2020-03-05T21:17:00

Solution 1

[EDIT]
According to the updated question:

Your database design is wrong! You need to use date data type instead of string , because none of database is able to compare string by using [>] and [<] operators!
See: MySQL :: MySQL 8.0 Reference Manual :: 12.3.2 Comparison Functions and Operators[^]

Imagine, what would be the result of below comparison:

"Andrew" > "Jane" AND "Mike" < "Anna"

None, due to the fact that strings are NOT comparable this way.

Note, that you can wrire custom function which gets two variables type of string in format: "MM-dd" and parse it to set of numbers. For "12-21" it should return {12, 21}. Than you should add a year part to create date. But(!) this solution in very inefficient.

So, final conclusion is: use proper data type - date!

Posted 5-Mar-20 21:17pm

Maciej Los

Updated 5-Mar-20 22:28pm

v4

Comments

Hello Infobekcons 6-Mar-20 3:47am

No this way i know already, by full date format i can search but i want to search via month-date format. I update my question database table. Please check

Maciej Los 6-Mar-20 4:08am

So, your database design is wrong! You need to use date data type instead of string (where it's a part of date).
None of database is able to compare string by using > and < operators!
See: MySQL :: MySQL 8.0 Reference Manual :: 12.3.2 Comparison Functions and Operators[^]

Hello Infobekcons 6-Mar-20 4:17am

Thanks for your reply, I already update the my from_date and to_date column date format. Using this https://stackoverflow.com/questions/22827579/change-the-date-format-in-phpmyadmin you can check here. My table column date format is now
%m %d

Richard Deeming 6-Mar-20 7:44am

It's not clear from the page you linked to, but I've just tried in SQL Server, and it's perfectly possible to compare strings using the < and > operators. They perform standard string comparisons, comparing each character in turn, depending on the applicable collation.

It's definitely the wrong thing to do for dates. But it's also wrong to say that strings can't be compared in this way.

Maciej Los 6-Mar-20 8:00am

Richard, i'm not saying that string comparison via usage of less-than and greater-than operator is not possible at all...

I would partially agree with your opinion, if OP had asking about sql server, but He/She is pointing to MySQL database server. Althought MySql is exposing STRCMP() function, but such of comparison does not meet OP's needs...
See: MySQL :: MySQL 8.0 Reference Manual :: 12.7.1 String Comparison Functions and Operators[^]

I'm talking about specific database provider...

CHill60 · Answer 2 · 2020-03-05T22:24:00

Hello Infobekcons wrote:
I also tried to change column date format to %m %d. but no solution

That is impossible. Date columns do not have a "format".

See the comments from @maciej-los - you should be storing the data in your table as date NOT varchar types. You can still search by month and year if you want to

SQL

select id from monthly_tool where MONTH(from_date) >= 12 AND YEAR(from_date) >= 22 and MONTH(to_date) <= 1 AND YEAR(to_date) <= 9

But that is not very nice. Better would be to convert the search parameters to dates e.g.

SQL

declare @search_from varchar(5) = '12-22'
declare @search_to varchar(5) = '01-18'
declare @date_from date = cast('01-' + @search_from AS date)
declare @date_to date = LASTDAY(cast('01-' + @search_to AS date))
select id from monthly_tool where from_date >= @date_from_date and to_date <= @date_to_date

[A point to make - your "from" dates are after your "to" dates in your "sample data"]

If you insist on storing your data incorrectly then at least store the characters in a way that is easily searchable e.g.

SQL

declare @monthly_tool table(id int identity(1,1), from_date varchar(5), to_date varchar(5))
insert into @monthly_tool (from_date, to_date) values
('2112','1901'),
('2001','1802')

declare @search_from varchar(5) = '2212'
declare @search_to varchar(5) = '1801'

select id from @monthly_tool where CAST(from_date as int) >= CAST(@search_from as int) and CAST(to_date as int) <= CAST(@search_to as date)

But the best advice you are going to get from all of us today is to store your data in the appropriate column types and the most appropriate column type for a date is date!