Click here to Skip to main content
15,032,967 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
select * from Performance_tb where format(Perf_Date,'MM/yyyy')='01/2020'


giving Perfect result
but when I used this

SQL
select * from Performance_tb where format(Perf_Date,'MM/yyyy')<'01/2020'

- SHowing wrong data.


MY DATA
Perf_Date
--------------------------------
2019-09-11 00:00:00.000
2019-09-12 00:00:00.000
2019-09-14 00:00:00.000
2019-09-15 00:00:00.000
2019-09-16 00:00:00.000
2019-09-17 00:00:00.000
2019-09-18 00:00:00.000
2019-09-19 00:00:00.000
2019-09-20 00:00:00.000
2019-09-21 00:00:00.000
2019-09-22 00:00:00.000
2019-09-23 00:00:00.000
2019-09-24 00:00:00.000
2019-09-25 00:00:00.000
2019-09-26 00:00:00.000
2019-09-27 00:00:00.000
2019-12-19 00:00:00.000
2019-12-20 00:00:00.000
2019-12-22 00:00:00.000
2019-12-23 00:00:00.000
2020-01-03 00:00:00.000
2020-01-04 00:00:00.000
2020-01-06 00:00:00.000
2020-01-07 00:00:00.000
2020-01-08 00:00:00.000
2020-01-09 00:00:00.000
2020-01-31 00:00:00.000
2020-02-01 00:00:00.000
2020-02-06 00:00:00.000
2020-02-07 00:00:00.000
2020-02-08 00:00:00.000
2020-02-17 00:00:00.000
2020-02-25 00:00:00.000
2020-03-01 00:00:00.000
2020-03-02 00:00:00.000

This is my data i want jan month data as well as i want before jan month data both case need to fire same query what can i do sir
my parameter is Month/Year i.e. 01/2020 so i want "=01/2020" or "<01/2020"


What I have tried:

SQL
select * from Performance_tb where format(Perf_Date,'MM/yyyy')<'01/2020'
Posted
Updated 4-Mar-20 22:08pm
v3
Comments
ZurdoDev 3-Mar-20 9:11am
   
Because you are converting the date to a string. Don't do that.
hareshdgr8 3-Mar-20 9:12am
   
sir how can i do if i want same code just need to change less then sign or other can you provide me
ZurdoDev 3-Mar-20 9:21am
   
select * from Performance_tb where Perf_Date <'2020-01-01'
hareshdgr8 3-Mar-20 9:22am
   
sir i dont know about day date only i get month and year value from other source
ZurdoDev 3-Mar-20 9:24am
   
Yes, but you want anything before January 2020, right? So, that's anything before 2020-01-01. It's the same thing.

You can also do
SELECT * FROM someTable WHERE YEAR(DATE) <= YEAR(@someDate) AND MONTH(DATE) < MONTH(@someDate)

But the first way is likely better in your case.
hareshdgr8 3-Mar-20 9:39am
   
Getting Wrong Output .. with this sir...
ZurdoDev 3-Mar-20 9:45am
   
Then just fix it. Not sure what you want us to do. You have a simple requirement, find dates that are before another date. I think you're making it more complicated.

I gave you 2 ways of doing it.
hareshdgr8 4-Mar-20 1:01am
   
i have sir only month and year how can i fix this
ZurdoDev 4-Mar-20 6:45am
   
Then use the MONTH() and YEAR() functions that I already suggested.

Don't format the date column; don't convert the date column; and don't try to call a function on the date column. None of these will be SARGable[^].

Instead, test for a range of dates.
SQL
DECLARE @Month date = '20200101';

-- Make sure we've got the first day of the month:
DECLARE @MonthStartInclusive date = DateAdd(day, 1 - Day(@Month), @Month);

-- Find the first day of the following month:
DECLARE @MonthEndExclusive date = DateAdd(month, 1, @MonthStartInclusive);

-- Data within the month:
SELECT * FROM Performance_tb WHERE Perf_Date >= @MonthStartInclusive And Perf_Date < @MonthEndExclusive;

-- Date before the start of the month:
SELECT * FROM Performance_tb WHERE Perf_Date < @MonthStartInclusive;

-- Data after the end of the month:
SELECT * FROM Performance_tb WHERE Perf_Date >= @MonthEndExclusive;
   
Comments
Maciej Los 4-Mar-20 6:50am
   
5ed!
You could try
SQL
SELECT * FROM Performance_tb WHERE YEAR(Perf_Date) < 2020
Or you could try to format the year/month to yyyyMM. This would lead to a valid integer representation which would preserve proper ordering.
SQL
SELECT * FROM Performance_tb WHERE CAST(FORMAT(Perf_Date, 'yyyyMM') AS int) < 202001
   
Comments
hareshdgr8 4-Mar-20 0:54am
   
reject because as per google said that never convert date field
https://www.brentozar.com/blitzcache/non-sargable-predicates/
ZurdoDev 4-Mar-20 6:46am
   
Google is wrong. There are times when you need to convert a date field, but probably not in your case.
hareshdgr8 4-Mar-20 6:49am
   
please check my solution sir I found from google and i applied no issue got perfect result
ZurdoDev 4-Mar-20 6:50am
   
So you are converting it. There's always a million ways to do things in development. Glad you found one that works for you.
phil.o 4-Mar-20 6:52am
   
And yet, the solution you proposed uses the CONVERT function. Richard Deeming's answer is the most appropriate here.
Quote:
SHowing wrong data.

You only forgot to tell what is wrong in result and what is expected.
Generally speaking, when you want to compare dates as strings, the format to use is "yyyymmdd" or "yyyy/mm/dd".
If I understand what you want, you need to use "yyyy/mm".
SQL
select * from Performance_tb where format(Perf_Date,'yyyy/MM')<'2020/01'

This gets anything before 2020.
   
Comments
hareshdgr8 4-Mar-20 0:32am
   
sir not getting proper data as per mine it should give me 49 records and for this query giving me 20 records only because the format will convert date into a string
Patrice T 4-Mar-20 5:25am
   
number of records do not help us to understand what is wrong in the result.
If you want to reject the time part, you can use:
SQL
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):
SQL
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
--or starting from MS SQL Server 2012
--SELECT DATEADD(DAY,1,EOMONTH(Perf_Date,-1))


[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:
SQL
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:
SQL
--create range of dates to this date (today)
DECLARE @enddate DATE = GETDATE();
--filter data based on this date => get data till the end of January
DECLARE @monthyear DATE = '2020-02-01';

--create range of dates
;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 
)
--get original date and date converted to YYYY-MM-01 format
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?
   
v3
Comments
hareshdgr8 4-Mar-20 7:06am
   
here if i want before 2020-01 data i dont have date sir where should i transfer because i want same month data as well as before month data also using single query that i want to use.
Maciej Los 4-Mar-20 7:09am
   
So, change the where condition:
WHERE Perf_Date < @somemonth

Try!
hareshdgr8 4-Mar-20 7:11am
   
sir still you not understand where i can pass this value CAST('2020-01-01' AS DATE)
i have only Year and Month like 2020-01
Maciej Los 4-Mar-20 7:14am
   
OK. Please, wait i'll improve my answer.
Richard Deeming 4-Mar-20 7:39am
   
DECLARE @YearAndMonth varchar(7) = '2020-01';
DECLARE @MonthStart date = CAST(@YearAndMonth + '-01' As date);
Maciej Los 4-Mar-20 7:52am
   
;)
hareshdgr8 4-Mar-20 8:07am
   
sir if i want full month data then this will not work at any more thats why i am just passing month and year only and my Field Perf_Date is datetime datatype
Richard Deeming 4-Mar-20 8:10am
   
What part is unclear?

You pass in the year and month: '2020-01'.

You add '-01' to that and cast it to a date to get the first day of that month.

You then use DateAdd to get the first day of the next month, and filter the records based on the date range - see solution 2 for details.

What part of that "won't work"?
hareshdgr8 4-Mar-20 8:54am
   
Sir If i want full month data then please help thats why its not working...
Richard Deeming 4-Mar-20 8:56am
   
How many times do we need to explain it to you?

And what, precisely, does "not working" mean?
Maciej Los 4-Mar-20 8:11am
   
So, use proper data type instead of converting it into string! As i already told you, when you convert it into text, you are going to have several problems.
In response to OP posting some actual data...

If that is your data then solution 3 is correct. There are only 20 rows in that data that are before January 2020. If you want to include January then use <=.

In response to that solution you stated that you were expecting 49 records returned but there are only 35 records in the data!

The only improvement that I would make is to do the conversion to a date outside the query itself (i.e. only cast once for efficiency purposes) e.g.
SQL
declare @parm varchar(8) = '01/2020'
declare @castparm date = CAST('01/' + @parm as DATE) -- results in 01/01/2020

select perf_date from @Performance_tb 
where Perf_Date < @castparm
In other comments you have suggested that the parameter would be in the format 2020-01. This is actually far better than 01/2020 as it ensures the casting to a date is unambiguous i.e.
SQL
declare @castparm date = CAST(@parm + '-01' as DATE) -- results in 2020-01-01
As noted by other members above dates like 01/04/2020 could be interpreted as 4th January or 1st April depending on regional settings, whereas 2020-04-01 is clearly 1st April.
   
Comments
hareshdgr8 5-Mar-20 4:10am
   
no sir while using declare @castparm date = CAST(@parm + '-01' as DATE) -- results in 2020-01-01 it give me only 1 date of data not whole month data like month of jan data
CHill60 5-Mar-20 4:13am
   
I did test my query and it returned the 20 rows I was expecting. The code you have quoted back to me is meant to return a single value - you then use that value to query the table!
Using this get the perfect result



select * from Performance_tb where convert(VARCHAR(7),Perf_Date,111) < '2020/04'
   
Comments
Maciej Los 4-Mar-20 6:51am
   
Sorry, but this is wrong!
hareshdgr8 4-Mar-20 6:57am
   
Otherwise sir please suggest me more solution
Maciej Los 4-Mar-20 7:04am
   
Please, see solution #5
hareshdgr8 4-Mar-20 6:53am
   
no sir from this got result properly..
Maciej Los 4-Mar-20 7:07am
   
That doesn't mean that you're doing it properly. Format of the date depends on many factors, such of localization, regional settins, etc.
Date format on MS SQL server might differ from user settings. This may cause several problem to you...
hareshdgr8 4-Mar-20 7:08am
   
okay sir can you please reply my query in Solution #5
CHill60 4-Mar-20 7:10am
   
This is truly awful. You would have been better off using Solution 3. If you had bothered to supply sample data then any problems could have been fixed for you.
hareshdgr8 4-Mar-20 7:13am
   
solution 3 is wrong sir that was my previous solution but not work properly no. of records not match with this .
CHill60 4-Mar-20 10:58am
   
As I said, you didn't provide us with any sample data to check any solutions
hareshdgr8 4-Mar-20 22:38pm
   
Perf_Date
--------------------------------
2019-09-11 00:00:00.000
2019-09-12 00:00:00.000
2019-09-14 00:00:00.000
2019-09-15 00:00:00.000
2019-09-16 00:00:00.000
2019-09-17 00:00:00.000
2019-09-18 00:00:00.000
2019-09-19 00:00:00.000
2019-09-20 00:00:00.000
2019-09-21 00:00:00.000
2019-09-22 00:00:00.000
2019-09-23 00:00:00.000
2019-09-24 00:00:00.000
2019-09-25 00:00:00.000
2019-09-26 00:00:00.000
2019-09-27 00:00:00.000
2019-12-19 00:00:00.000
2019-12-20 00:00:00.000
2019-12-22 00:00:00.000
2019-12-23 00:00:00.000
2020-01-03 00:00:00.000
2020-01-04 00:00:00.000
2020-01-06 00:00:00.000
2020-01-07 00:00:00.000
2020-01-08 00:00:00.000
2020-01-09 00:00:00.000
2020-01-31 00:00:00.000
2020-02-01 00:00:00.000
2020-02-06 00:00:00.000
2020-02-07 00:00:00.000
2020-02-08 00:00:00.000
2020-02-17 00:00:00.000
2020-02-25 00:00:00.000
2020-03-01 00:00:00.000
2020-03-02 00:00:00.000

This is my data i want jan month data as well as i want before jan month data both case need to fire same query what can i do sir
my parameter is Month/Year i.e. 01/2020 so i want "=01/2020" or "<01/2020"
Maciej Los 5-Mar-20 5:14am
   
I've created fiddle for you: SQL Fiddle[^]. Check it out. This is working!!!

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900