If you do this what value do you see for @yr?
DECLARE @yr AS date;
SET @yr = dbo.DateSerial(YEAR(GETDATE()), 6, 30)
SELECT [@yr] = @yr
I presume you should see 30-June-2015 ?
If so then
WHERE (p.EndDate >= @yr)
will only select [EndDate] which is on, or after, 30-June-2015. But that said I would expect that you only want after 30-June-2015 and not also including that date? as it looks like an end-of-period date.
It is NOT showing anything greater than 6/30/2015.
I need it to show >= 6/30/currentyear [2015]
Repeating my earlier request:
P.S> Just Belt & Braces but what is the datatype of the [EndDate] column? Presumably DATE or DATETIME? (If not the ">=" might well not work as expected, but could still work just fine with functions like YEAR(EndDate) )
It's DateTime.
2015 only.
Need to see >=current year
If
SET @yr = dbo.DateSerial(YEAR(GETDATE()), 6, 30)
returns EXACTLY "2015" then
WHERE (p.EndDate >= @yr)
will not return dates after 01-Jan-2015.
Here's a test:
SELECT MIN(p.EndDate), MAX(p.EndDate)
FROM YourTable
WHERE p.EndDate >= 2015
because this query will make an implicit conversion from DATE / DATETIME to INT and DATETIME is stored as a composite of the number of days since 1900 and the number of milliseconds since the start of the day (or something like that). Either way, pretty much every datetime will have a value bigger than 2015 ...
My test here shows:
SELECT CONVERT(datetime, 2015)
gives
1905-07-09 00:00:00.000
If that's the case how, and one reason why i don't want use the Declaration, so I ask again, what do you think can be changed so that I can use the >=DateSerial(Year(GetDate()), 6, 30)? Which I know produces all the years for 6/30 I need.
So tried this
SELECT MIN(p.EndDate), MAX(p.EndDate)
FROM dbo.PersonMembership AS p
WHERE p.EndDate >= 2015and I get the following:
2015-02-05 00:00:00.000 2019-06-30 00:00:00.000
Which is great, except I need to limit it to only 6/30/yyyy as mentioned.
So I changed it to
SELECT MIN(p.EndDate), MAX(p.EndDate)
FROM dbo.PersonMembership AS p
WHERE p.EndDate >=dbo.DateSerial(Year(GetDate()),6,30)
which produces what I want, i did mention that this function works, so not sure what you're looking for??
2015-06-30 00:00:00.000 2019-06-30 00:00:00.000
The issue I'm having is utilizing this into the formula to make the whole thing produce the results correctly.
However it won't produce the > part of it in the formula which includes all of the results correctly. It's only correct in individual year is set. This is what I'm having problems with.
As mentioned, in a basic, simple query the >=DateSerial works. And just proved it here.
So if this is the case, how can it be changed so it works as shown here?
WHERE (p.EndDate >= @yr)
will not return dates after 01-Jan-2015.
Thanks for your help.
Attempts are greatly appreciated!
So fyi - I got it to work with one of the formulas. I realize now that it's not that it's not accepting the >= but that the result produced is not correct.
As noted in the OUTER JOIN post where this post branches off from, the result is only correct, at least for this one particular ClubNo 5305, when it's set to individual years.
That's the problem. Why when wanting too see multiple years this club won't show the no membership year when it does on the individually set year?
When I try
DECLARE @yr AS date;
SET @yr = dbo.DateSerial(YEAR(GETDATE()), 6, 30)
SELECT [@yr]=@yr
here (using the source for DateSerial that you posted in your other thread here) I get:
2015-06-30
and NOT just "2015"
I tried declaring @yr as INT, but I then get an error attempting to assigning DateSerial to @yr
I just want to double check that you got just "2015" in the earlier test?
May not be the answer to the right question?? but you can get "year" like this:
SELECT DateAdd(Year, DateDiff(Year, 0, GetDate()), 0)
That will give you 01-Jan for the current year. You could then add 6 months -1 days to that to get 30-June:
SELECT DateAdd(Day, -1, DateAdd(Month, 6, DateAdd(Year, DateDiff(Year, 0, GetDate()), 0)))
BUt ... I can;t see anything wrong with your DateSerial function, and it seems the issue is with the @YR variable and the
p.EndDate >= @yr
test, so using a different "Date Calculation Formula" is probably not the answer.
Hi,
I got the >= to work.
it's just not producing the combination of the years result for some reason.
So it's now back to the original formula with this date parameter. Since the date parameter itself works. just that the data produced with the range isn't always correct.
Would like to see if I change this
p.EndDate >= GETDATE() AND GETDATE() >= DATEADD(month, - 6 - CASE WHEN p.MembershipTypeId = 3 THEN 4 ELSE 0 END, DATEADD(year, DATEDIFF(year, 1, DATEADD(month, DATEDIFF(month, 0, p.EndDate) + 6, 0)), 0))
with this to see if it works:
I tried but failing.
The 1st code produces only 2016 results
Hi I got it to work with the following ..
SELECT MIN(p.EndDate)
,MAX(p.EndDate)
FROM (
SELECT *
FROM #SampleData
WHERE year(EndDate) >= 2015
) p
Please find my sample data below
/*
IF OBJECT_ID('tempdb.dbo.#SampleData', 'U') IS NOT NULL
drop table #SampleData;
GO
create table #SampleData
(
EndDate datetime
)
GO
insert into #SampleData select '2014-10-03'
insert into #SampleData select '2013-05-23'
insert into #SampleData select '2015-05-23'
insert into #SampleData select '2015-12-18'
insert into #SampleData select '2015-09-05'
insert into #SampleData select '2015-08-21'
GO
select * from #SampleData
GO
*/
Yes, that would work as expected (hopefully!!)
The issue was that in one place @yr was declared as INT and in another as DATETIME. The O/P was seem some data "out of range" included in the results and I wondered if by accident the code was doing
WHERE EndDate >= 2015
SQL does NOT complain about this, but makes a test using the internal integer format that date/time is stored in and
SELECT CONVERT(datetime, 2015)
gives
1905-07-09 00:00:00.000
so
WHERE EndDate >= 2015
will include anything after 1905-07-09 
I wish there was a VERBOSE or similar mode which warned on all these types of things - or a EXPLICIT / STRICT setting that disallowed them. They must, collective, take hours and hours for people to spot and fix. WAY too many such silent implicit conversions exist in SQL IMHO.
For example this gives a TRUE result
DECLARE @MyDateTime datetime
SELECT @MyDateTime = GetDate()
SELECT CASE WHEN @MyDateTime > 2016 THEN 1 ELSE 0 END
Indeed, CONVERT(int, GetDate()) gives 42236 so this is true also
DECLARE @MyDateTime datetime
SELECT @MyDateTime = GetDate()
SELECT CASE WHEN @MyDateTime > 9999 THEN 1 ELSE 0 END
I tried running that through Ubitsoft's excellent SQL code analyser / "Lint" checker.
http://www.ubitsoft.com/products/t-sql-analyzer/index.php
Sadly it didn't object 
Indeed! Another variation on this theme is:
WHERE EndDate >= 11/30/2015
and then there is
WHERE EndDate >= 20151130
The second one at least gives a overflow error or something like that. DATE data type is better than DATETIME in this regard, in that it allows fewer implicit conversions.
How do I close this thread?
We don't really have a facility for that here. You could change the Subject to add "[Resolved]" perhaps?