Declare date range for variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Declare date range for variable

Author

Topic

Dale45039
Starting Member

41 Posts

Posted - 2012-07-16 : 11:30:08

I'm tring to declare a variable that is between two dates. The error I am getting is: Incorrect syntax near the keyword 'Between'. Here is the declaration I am using:

DECLARE @DateTime varchar
SET @DateTime = (vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-09 23:59:59' And
'2012-07-10 23:59:59')

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 11:34:46

you cant have variable that stores rnage of values. you should be using table variable for this.

ie like


declare @DateTable table
(
DateVal datetime
)

insert @dateTable
select datecol
from vwGenCustApptInfo
where Appt_Acknowledged_Date >='2012-07-10' 
And Appt_Acknowledged_Date < '2012-07-11'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 11:39:57

What do you want to do with the variable?
Are yoou looking for two variables - @startdate, @enddate and use those in a query?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Dale45039
Starting Member

41 Posts

Posted - 2012-07-16 : 11:58:39

quote:
Originally posted by nigelrivett

What do you want to do with the variable?
Are yoou looking for two variables - @startdate, @enddate and use those in a query?

I have multiple instances of (vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-09 23:59:59' And
'2012-07-10 23:59:59')
in my query and would like to declare it as a variable at the beginning. If I wanted to change the date range in the future, I would only have to change the variable.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 12:02:01

declare @startdate datetime = '2012-07-09 23:59:59'
declare @enddate datetime = '2012-07-10 23:59:59'

select .....
from vwGenCustApptInfo
...
where vwGenCustApptInfo.Appt_Acknowledged_Date between @startdate and @enddate
...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Dale45039
Starting Member

41 Posts

Posted - 2012-07-16 : 12:08:19

quote:
Originally posted by visakh16

you cant have variable that stores rnage of values. you should be using table variable for this.

ie like

declare @DateTable table
(
DateVal datetime
)

insert @dateTable
select datecol
from vwGenCustApptInfo
where Appt_Acknowledged_Date >='2012-07-10'
And Appt_Acknowledged_Date < '2012-07-11'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

What would I use as the variable (@dateTable?) in the rest of the query? Like in a WHERE statement like this:

Where
(vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-09 23:59:59' And '2012-07-10 23:59:59')

Should it be:

Where (@dateTable)

?

Dale45039
Starting Member

41 Posts

Posted - 2012-07-16 : 16:23:01

quote:
Originally posted by nigelrivett

declare @startdate datetime = '2012-07-09 23:59:59'
declare @enddate datetime = '2012-07-10 23:59:59'

select .....
from vwGenCustApptInfo
...
where vwGenCustApptInfo.Appt_Acknowledged_Date between @startdate and @enddate
...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

This is strange - it won't work with SQL Server 2005, but works just fine with SQL Server 2008. Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 22:04:28

quote:
Originally posted by Dale45039

quote:
Originally posted by nigelrivett

declare @startdate datetime = '2012-07-09 23:59:59'
declare @enddate datetime = '2012-07-10 23:59:59'

select .....
from vwGenCustApptInfo
...
where vwGenCustApptInfo.Appt_Acknowledged_Date between @startdate and @enddate
...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

This is strange - it won't work with SQL Server 2005, but works just fine with SQL Server 2008. Any ideas?

yep. 2005 doesnt allow inline assignment of values

so in 2005 it should be


declare @startdate datetime 
declare @enddate datetime 


select @startdate = '2012-07-09 23:59:59',
@enddate = '2012-07-10 23:59:59'
 
select .....
from vwGenCustApptInfo
...
where vwGenCustApptInfo.Appt_Acknowledged_Date between @startdate and @enddate
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 22:06:00

quote:
Originally posted by Dale45039

quote:
Originally posted by visakh16

you cant have variable that stores rnage of values. you should be using table variable for this.

ie like

declare @DateTable table
(
DateVal datetime
)

insert @dateTable
select datecol
from vwGenCustApptInfo
where Appt_Acknowledged_Date >='2012-07-10'
And Appt_Acknowledged_Date < '2012-07-11'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

What would I use as the variable (@dateTable?) in the rest of the query? Like in a WHERE statement like this:

Where
(vwGenCustApptInfo.Appt_Acknowledged_Date Between '2012-07-09 23:59:59' And '2012-07-10 23:59:59')

Should it be:

Where (@dateTable)

?

nope as its a table you can either add it to join on condition

vwGenCustApptInfo.Appt_Acknowledged_Date = table.datefield

or use IN

Where vwGenCustApptInfo.Appt_Acknowledged_Date IN (SELECT datecolumn FROM @dateTable)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources