SQL Date column issues in query

Question

Post reply

SQL Date column issues in query

mtz676

SSCrazy Eights

Points: 9212
More actions
May 26, 2016 at 12:18 pm

#314143

declare @startdate datetime
set @startdate = '01-01-2016'
declare @enddate datetime
set @startdate = '10-10-2016'
IF(@startdate is NULL or @enddate is NULL)
BEGIN
select * from TableA A
inner join TableB B
on A.col1=B.col2
WHERE
A.col2='xxx'
and
(( A.col3 in ('AAA') or A.col3= 'NULL')
OR
(A.col4 in ('BBB') or A.col4= 'NULL'))
END
ELSE
BEGIN
select * from TableA A
inner join TableB B
on A.col1=B.col2
WHERE
A.col2='xxx'
and
(( A.col3 in ('AAA') or A.col3= 'NULL')
OR
(A.col4 in ('BBB') or A.col4= 'NULL'))
AND
(A.Dt between @startdate and @enddate))
END
When I plug in start and enddates it is not working properly.It is returning data irrepective of the dates supplied.
A.Dt is a datetime column.
Any suggestions please.

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

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

dogramone Hall of Fame Points: 3216 More actions · Answer 1

Try running with the variable dates in dd-mon=ccyy format to help with debugging, ie '01 jan 1900

ScottPletcher SSC Guru Points: 101096 More actions · Answer 2

You SET @startdate twice instead of setting @enddate 🙂

..

set @startdate = '01-01-2016'

declare @enddate datetime

set @startdate = '10-10-2016' --should be set @enddate = ...

...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

drew.allen SSC Guru Points: 76985 More actions · Answer 3

You're setting your @startdate twice, so the @enddate is NULL.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76985 More actions · Answer 4

dogramone (5/26/2016)
Try running with the variable dates in dd-mon=ccyy format to help with debugging, ie '01 jan 1900

Do not EVER do this. I believe that there are only two formats that are guaranteed to be locale independent: 'YYYYMMDD hh:mm:ss' and 'YYYY-MM-DDThh:mm:ss' (note the "T" between the date portion and the time portion).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

drew.allen (5/26/2016)
dogramone (5/26/2016)
Try running with the variable dates in dd-mon=ccyy format to help with debugging, ie '01 jan 1900
Do not EVER do this. I believe that there are only two formats that are guaranteed to be locale independent: 'YYYYMMDD hh:mm:ss' and 'YYYY-MM-DDThh:mm:ss' (note the "T" between the date portion and the time portion).
Drew

+100000

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dogramone Hall of Fame Points: 3216 More actions · Answer 6

Thanks Luiz and Drew for puting me straight, much appreciated.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

Piling on, don't use implicit convertion

😎

DECLARE @startdate DATETIME = CONVERT(DATETIME,'20160101',112);

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 8

oops...thanks ... feel stupid now.Thanks anyway !

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 9

The above code is part of an SSRS report.After setting variables properly it is still not filtering data based of the date column.

HappyGeek SSCoach Points: 18729 More actions · Answer 10

When creating SSRS reports with date ranges I found the technique described in http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/133147/ works very well, particularly from a user perspective.

...

9423t8 Mr or Mrs. 500 Points: 507 More actions · Answer 11

You should ideally create two Parameters in your SSRS Report for the dates. If the dates are semi-static or are 'relative' dates (today, yesterday, a fortnight ago), use an Expression in the Parameter's Default Properties to set the dates; otherwise the user can choose dates at run time. For 'choosable' dates, set a Default value of NULL; for 'fixed' dates, make the Parameter Internal so that the user isn't prompted for it.

Suppose you name your parms. @From and @To, then at the top of your Database query you would write:

DECLARE @startdate AS DATETIME

DECLARE @enddate as DATETIME

SET @startdate = @From

SET @enddate = @To

This eliminates all date constant shenanigans (though when required, as others have said, RIGOROUSLY use the YYYY-MM-DDThh:mm:ss format to avoid any problems).

PS: Yes, HappyGeek is right about John Tamburo's 'dynamic date' concept and I've used an adapted version of that myself with great success in some reports. It's not a universal panacea though, and more savvy users who know the exact dates they want will often — in my experience — prefer using a plain old standard calendar picker.

Sergiy SSC Guru Points: 110208 More actions · Answer 12

sqlnewbie17 (5/26/2016)
The above code is part of an SSRS report.After setting variables properly it is still not filtering data based of the date column.