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*
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.
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) )
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 ...
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.
SELECT MIN(p.EndDate), MAX(p.EndDate)
FROM dbo.PersonMembership AS p
WHERE p.EndDate >= 2015
and 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?
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?
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.
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
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
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
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.
