Can't use >= with setInt

Question

Can't use >= with setInt

serviceaellis

SSCommitted

Points: 1865
More actions
August 19, 2015 at 8:00 pm

#299927

Currently working with
DECLARE @yr AS int;
SET @yr = 2015;
which is used in
WHERE (YEAR(EndDate) = @yr)
AND (MONTH(EndDate) = 6)
AND (DAY(EndDate) = 30)
The result is correct when working with year by year (when I change it to 2016) but not for a range, which is the below.
When I change it to the following I do not get the same results. I can't figure out why.
I need the result to show the correct data in a range as it shows for individual year.
DECLARE @yr AS date;
SET @yr = dbo.DateSerial(YEAR(GETDATE()), 6, 30); <--- note: I have a Function and this is verified and works in a simple query, just apparently not in this
WHERE (p.EndDate >= @yr)
Also, would prefer to not DECLARE

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 1

serviceaellis (8/19/2015)
Currently working with
DECLARE @yr AS int;
SET @yr = 2015;
which is used in
WHERE (YEAR(EndDate) = @yr)
AND (MONTH(EndDate) = 6)
AND (DAY(EndDate) = 30)
The result is correct when working with year by year (when I change it to 2016) but not for a range, which is the below.
When I change it to the following I do not get the same results. I can't figure out why.
I need the result to show the correct data in a range as it shows for individual year.
DECLARE @yr AS date;
SET @yr = dbo.DateSerial(YEAR(GETDATE()), 6, 30); <--- note: I have a Function and this is verified and works in a simple query, just apparently not in this
WHERE (p.EndDate >= @yr)
Also, would prefer to not DECLARE

Not sure that I'm bagging what you're raking. Are you trying to return data where the have a range of years and want to return the data from Jun 30th for every year in that range? And how does GETDATE() enter the picture insofar as a range of years?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

serviceaellis SSCommitted Points: 1865 More actions · Answer 2

lol

yes on the date range.

not sure what you're asking on the GetDate()

other then using it with Year in order to get the current year by using Year(GetDate())

the issue i'm having is the formula works only with individual year by either having 2015 or 2016 or Year(GetDate()) or Year(GetDate())+1 but not when trying to do >=Year(GetDate()).

I can't figure out why that is.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

serviceaellis (8/19/2015)
Currently working with
DECLARE @yr AS int;
SET @yr = 2015;
which is used in
WHERE (YEAR(EndDate) = @yr)
AND (MONTH(EndDate) = 6)
AND (DAY(EndDate) = 30)
The result is correct when working with year by year (when I change it to 2016) but not for a range, which is the below.
When I change it to the following I do not get the same results. I can't figure out why.
I need the result to show the correct data in a range as it shows for individual year.
DECLARE @yr AS date;
SET @yr = dbo.DateSerial(YEAR(GETDATE()), 6, 30); <--- note: I have a Function and this is verified and works in a simple query, just apparently not in this
WHERE (p.EndDate >= @yr)
Also, would prefer to not DECLARE

The single most important piece of information here, without which every answer can only be a guess, is the value returned to @yr by your dbo.DateSerial() function. Is it secret?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

ChrisM@Work (8/20/2015)
serviceaellis (8/19/2015)
Currently working with
DECLARE @yr AS int;
SET @yr = 2015;
which is used in
WHERE (YEAR(EndDate) = @yr)
AND (MONTH(EndDate) = 6)
AND (DAY(EndDate) = 30)
The result is correct when working with year by year (when I change it to 2016) but not for a range, which is the below.
When I change it to the following I do not get the same results. I can't figure out why.
I need the result to show the correct data in a range as it shows for individual year.
DECLARE @yr AS date;
SET @yr = dbo.DateSerial(YEAR(GETDATE()), 6, 30); <--- note: I have a Function and this is verified and works in a simple query, just apparently not in this
WHERE (p.EndDate >= @yr)
Also, would prefer to not DECLARE
The single most important piece of information here, without which every answer can only be a guess, is the value returned to @yr by your dbo.DateSerial() function. Is it secret?

relates to this post and thread me thinks

http://www.sqlservercentral.com/Forums/FindPost1713175.aspx

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

serviceaellis SSCommitted Points: 1865 More actions · Answer 5

ChrisM@Work (8/20/2015)
Also, would prefer to not DECLARE
The single most important piece of information here, without which every answer can only be a guess, is the value returned to @yr by your dbo.DateSerial() function. Is it secret?

? This it, with = it returns 6/30/2015 with >= it returns greater or equals to 6/30/2015

J Livingston SQL,

yes, seeing if someone else just looking at that part can figure out.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

I might be missing something, but it doesn't seem so mysterious why those don't return the same results.

I'm assuming here that you're actually comparing the second version to this (from the other thread):

WHERE (YEAR(EndDate) >= @yr)

AND (MONTH(EndDate) = 6)

AND (DAY(EndDate) = 30)

The first one specifies that the month and day of EndDate have to be June 30th, and that the year has to be at least 2015.

The second only specifies that the EndDate has to be greater than or equal to June 30th, 2015. There's nothing restricting the month and day to June 30th, so the results will be different if there are month/day combinations other than 6/30 that are after 6/30/2015.

If you need to restrict the matching dates to June 30th of various years, you'll need to specify that somehow. You could do it like you do in the first WHERE clause. Alternatively you could just generate the dates for June 30th of all the years you're interested in, and then use an IN clause to restrict the EndDate accordingly. That will be more seek-friendly than chopping up the date and making it non-SARGable, but a tad more involved to write (still not so bad, though).

Cheers!

serviceaellis SSCommitted Points: 1865 More actions · Answer 7

Jacob Wilkins (8/20/2015)
I might be missing something, but it doesn't seem so mysterious why those don't return the same results.
I'm assuming here that you're actually comparing the second version to this (from the other thread):
WHERE (YEAR(EndDate) >= @yr)
AND (MONTH(EndDate) = 6)
AND (DAY(EndDate) = 30)
The first one specifies that the month and day of EndDate have to be June 30th, and that the year has to be at least 2015.
The second only specifies that the EndDate has to be greater than or equal to June 30th, 2015. There's nothing restricting the month and day to June 30th, so the results will be different if there are month/day combinations other than 6/30 that are after 6/30/2015.
If you need to restrict the matching dates to June 30th of various years, you'll need to specify that somehow. You could do it like you do in the first WHERE clause. Alternatively you could just generate the dates for June 30th of all the years you're interested in, and then use an IN clause to restrict the EndDate accordingly. That will be more seek-friendly than chopping up the date and making it non-SARGable, but a tad more involved to write (still not so bad, though).
Cheers!

Why trying to use the Function for DateSerial(). Which works in a simple query but not in any of these formulas.

Are you able to make that work? I can't be sure that will produce what i need.

All i know is that any of the formulas so far, that seems to be correct BUT only for individual year when declared.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

My apologies, but I'm not really following what you're saying.

Perhaps it would be best if you posted a small sample data set for this thread that illustrates the incorrect results you're seeing. I'll probably be able to help better then.

Cheers!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

serviceaellis (8/20/2015)
Jacob Wilkins (8/20/2015)
I might be missing something, but it doesn't seem so mysterious why those don't return the same results.
I'm assuming here that you're actually comparing the second version to this (from the other thread):
WHERE (YEAR(EndDate) >= @yr)
AND (MONTH(EndDate) = 6)
AND (DAY(EndDate) = 30)
The first one specifies that the month and day of EndDate have to be June 30th, and that the year has to be at least 2015.
The second only specifies that the EndDate has to be greater than or equal to June 30th, 2015. There's nothing restricting the month and day to June 30th, so the results will be different if there are month/day combinations other than 6/30 that are after 6/30/2015.
If you need to restrict the matching dates to June 30th of various years, you'll need to specify that somehow. You could do it like you do in the first WHERE clause. Alternatively you could just generate the dates for June 30th of all the years you're interested in, and then use an IN clause to restrict the EndDate accordingly. That will be more seek-friendly than chopping up the date and making it non-SARGable, but a tad more involved to write (still not so bad, though).
Cheers!
Why trying to use the Function for DateSerial(). Which works in a simple query but not in any of these formulas.
Are you able to make that work? I can't be sure that will produce what i need.
All i know is that any of the formulas so far, that seems to be correct BUT only for individual year when declared.

First, can EndDate have any date other than YYYY/06/30? If not and you want all dates greater than or equal to 2015/06/30 why aren't you just doing this:

...

EndDate >= '2015/06/30'

...

serviceaellis SSCommitted Points: 1865 More actions · Answer 10

Lynn Pettis (8/20/2015)
First, can EndDate have any date other than YYYY/06/30? If not and you want all dates greater than or equal to 2015/06/30 why aren't you just doing this:
...
EndDate >= '2015/06/30'
...

It does, hence using the DateSerial(Year(GetDate()), 6, 30)

And wanting to use >=DateSerial(Year(GetDaee()), 6, 30).

I know that this produces the correct info in a simple query but not in the one I'm working with with the Declaration for some reason.

serviceaellis SSCommitted Points: 1865 More actions · Answer 11

Jacob Wilkins (8/20/2015)
My apologies, but I'm not really following what you're saying.
Perhaps it would be best if you posted a small sample data set for this thread that illustrates the incorrect results you're seeing. I'll probably be able to help better then.
Cheers!

I can't figure out how to insert the sample table of the results.

Either way the >= is NOT showing the >, only the = in the result.

Which is the problem.

It works in a basic, simple query but not with the Declaration usage for whatever reason I do not know.