SQLTeam.com | Weblogs | Forums

Can't use >= with DECLARE and SET


#1

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


#2

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.


#3

It is NOT showing anything greater than 6/30/2015.

I need it to show >= 6/30/currentyear [2015]


#4

Repeating my earlier request:


#5

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


#6

It's DateTime.


#7

2015 only.

Need to see >=current year


#8

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


#9

My test here shows:

SELECT CONVERT(datetime, 2015)
gives
1905-07-09 00:00:00.000

#10

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.


#11

So tried this

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?

WHERE (p.EndDate >= @yr)

will not return dates after 01-Jan-2015.


#12

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?


#13

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.


#14

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.


#15

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


#16

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
*/


#17

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 :frowning:

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 :frowning:

DECLARE @MyDateTime datetime
SELECT @MyDateTime = GetDate()

SELECT CASE WHEN @MyDateTime > 9999 THEN 1 ELSE 0 END

#18

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 :frowning:


#19

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.


#20

How do I close this thread?