Compare date with by month & year in SQL server

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

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 3-Mar-20 3:01am

hareshdgr8

Updated 4-Mar-20 22:08pm

v3

Add a Solution

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.

6 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)

Richard Deeming · Answer 1 · 2020-03-03T04:08:00

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;

phil.o · Answer 2 · 2020-03-03T03:23:00

Solution 1

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

Posted 3-Mar-20 3:23am

phil.o

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.

Patrice T · Answer 3 · 2020-03-03T06:57:00

Solution 3

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.

Posted 3-Mar-20 6:57am

Patrice T

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.

Maciej Los · Answer 4 · 2020-03-04T01:03:00

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?

CHill60 · Answer 5 · 2020-03-04T22:08:00

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.

hareshdgr8 · Answer 6 · 2020-03-04T00:48:00

Solution 4

Using this get the perfect result

select * from Performance_tb where convert(VARCHAR(7),Perf_Date,111) < '2020/04'

Posted 4-Mar-20 0:48am

hareshdgr8

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

Compare date with by month & year in SQL server

6 solutions

Solution 2

Solution 1

Solution 3

Solution 5

Solution 7

Solution 4

Add your solution here

Preview 0