Date Help

Question

Date Help

rocky_498

SSCertifiable

Points: 6532
More actions
January 15, 2014 at 8:20 pm

#302851

Hi,
I would like to know, is there is any difference between Option 1 and Option 2 or not?
Option 1
Declare @StartDate Date
Declare @EndDate Date
Set @StartDate = '20140115'
Set @EndDate = '20140114'
Option 2
Declare @StartDate Date
Declare @EndDate Date
Set @StartDate = '20140114'
Set @EndDate = '20140115'

Viewing 7 posts - 1 through 6 (of 6 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Do you mean 'Does it matter the order in which I declare variables and assign them values?' No. Not unless one is dependent upon another.

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

Yes, I am so confused, Because I am getting different results if I switch values. See Example

Date

1/15/2014

1/14/2014

1/15/2014

1/11/2014

Set @StartDate = '1/15/2014'

Set @EndDate = '1/14/2014'

Or

Set @StartDate = '1/14/2014'

Set @EndDate = '1/15/2014'

if my filter is Date between @StartDate and @EndDate

I am goona receive below value, am i right?

Date

1/15/2014

1/14/2014

1/15/2014

pietlinden SSC Guru Points: 63503 More actions · Answer 3

This pretty much explains it -- just run the queries and see what happens:

USE tempdb;

GO

CREATE TABLE SomeDates(TheDate DATE);

INSERT INTO SomeDates VALUES('1/11/2014');

INSERT INTO SomeDates VALUES('1/12/2014');

INSERT INTO SomeDates VALUES('1/13/2014');

INSERT INTO SomeDates VALUES('1/14/2014');

INSERT INTO SomeDates VALUES('1/15/2014');

INSERT INTO SomeDates VALUES('1/16/2014');

DECLARE @StartDate DATE,

@EndDate DATE;

/*

Set @StartDate = '1/14/2014';

Set @EndDate = '1/15/2014';

SELECT *

FROM SomeDates

WHERE TheDate BETWEEN @StartDate AND @EndDate;

-- returns 2014-01-14, 2014-01-15

*/

Set @StartDate = '1/15/2014'

Set @EndDate = '1/14/2014'

/* returns no values so @StartDate should be <= @EndDate

SELECT *

FROM SomeDates

WHERE TheDate BETWEEN @StartDate AND @EndDate;

What were you expecting to happen? What I concluded when trying the second one was that IF @EndDate>=@StartDate, then it would work fine. If @EndDate<@StartDate, then no records.

rocky_498 SSCertifiable Points: 6532 More actions · Answer 4

I really appreciate your help. I need to change my SP Values and it will fix my problem.

Thank You,

pietlinden SSC Guru Points: 63503 More actions · Answer 5

Great! Glad to help.

When in doubt, test it out in TEMPDB.

Either use

USE TEMPDB;

GO

... and then paste your code here

OR prefix the tablename with a # sign....

CREATE TABLE #TableInTempDB (....);

INSERT INTO #TableInTempDB...

SELECT * FROM #TableInTempDB;

Then when your session ends, (or you explicitly drop the tables), you can test stuff out there before you make a critical mistake.

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

Think of BETWEEN as the following:

...

where

FilterColumn >= @StartDate and FilterColumn <= @EndDate

Would this make sense if @StartDate was greater than @EndDate?