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
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.
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.
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
!