If you want to reject the time part, you can use:
SELECT CAST(Perf_Date AS DATE) JustDateWithoutTimePart
If you want to reject the day part, i would suggest to replace it with predefined value (for eaxmple first day of month) to be able to compare to the other date (month and year only):
DECLARE @somemonth DATE = CAST('2020-01-01' AS DATE);
SELECT DATEADD(MM, DATEDIFF(MM, 0, Perf_Date), 0) AS MonthAndYearWithPredefinedDay
FROM Performance_tb
WHERE Perf_Date = @somemonth
[EDIT]
hareshdgr8 wrote:
i have only Year and Month like 2020-01
This is very bad practice to store dates as a text! This is the reason of several problems!
I'd strongly recommend to change
Perf_Date
field to proper date type.
At this moment, you can do something like that:
SET DATEFORMAT ymd;
DECLARE @enddate DATE = CAST('2020-01-01' AS DATE)
;WITH SemiDates AS
(
SELECT '2020-02' SemiDate
UNION ALL
SELECT '2020-01' SemiDate
UNION ALL
SELECT '2019-10' SemiDate
UNION ALL
SELECT '2019-12' SemiDate
UNION ALL
SELECT '2018-11' SemiDate
UNION ALL
SELECT '2018-05' SemiDate
)
SELECT DATEFROMPARTS(CAST(LEFT(SemiDate, 4) AS INT), CAST(RIGHT(SemiDate, 2) AS INT), 1) As ShortDate
FROM SemiDates
WHERE DATEFROMPARTS(CAST(LEFT(SemiDate, 4) AS INT), CAST(RIGHT(SemiDate, 2) AS INT), 1) <@enddate
[EDIT2]
Last chance...
Take a look at below query:
DECLARE @enddate DATE = GETDATE();
DECLARE @monthyear DATE = '2020-02-01';
;WITH SetOfDates AS
(
SELECT CAST('2019-12-20' AS DATE) Perf_Date
UNION ALL
SELECT DATEADD(DD, 1, Perf_Date) Perf_Date
FROM SetOfDates
WHERE DATEADD(DD, 1, Perf_Date) <= @enddate
)
SELECT Perf_Date, CAST(DATEADD(MM, DATEDIFF(MM, 0, Perf_Date), 0) AS DATE) AS MonthAndYearWithPredefinedDay
FROM SetOfDates
WHERE DATEADD(MM, DATEDIFF(MM, 0, Perf_Date), 0)< @monthyear;
This produces:
2019-12-20 2019-12-01
2019-12-21 2019-12-01
...
2019-12-31 2019-12-01
2020-01-01 2020-01-01
...
2020-01-31 2020-01-01
which means that entire set of of data is fetched till the end of January.
Do you understand it now?