Declare Statements - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Declare Statements

Author

Topic

sonjan
Starting Member

22 Posts

Posted - 2011-11-01 : 21:35:50

Hi
I would like to use a declare statement for a datetime variable - particulary between dates. This is the script I've written so far, which doesn't work:

declare @StartDate datetime (null)
set @StartDate = '2011-07-01 00:00:00'

declare @EndDate datetime (null)
set @EndDate = '2011-11-01 00:00:00'

select
workorder.wonum,
convert(varchar, workorder.reportdate, (103)) as report_date
from
workorder with (nolock)
inner join
failurereport with (nolock) on failurereport.wonum = workorder.wonum and failurereport.type = 'CAUSE' and failurereport.failurecode in ('C_GRAFF', 'C_VANDAL', 'C_THEFT', 'C_INCIDE', 'C_UNSEC')
where
workorder.reportdate between @StartDate and @EndDate

Error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'null'.
Msg 137, Level 15, State 1, Line 4
Must declare the variable '@StartDate'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'null'.
Msg 137, Level 15, State 1, Line 7
Must declare the variable '@EndDate'

Any assistance would be greatly appreciated. Do I need to convert the declare variables?
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-01 : 21:49:02

Where did you get such syntax ?

delete the (null)


declare @StartDate datetime (null)
set @StartDate = '2011-07-01 00:00:00'

declare @EndDate datetime (null)
set @EndDate = '2011-11-01 00:00:00'


declare @StartDate datetime (null)  = '2011-07-01 00:00:00'
set @StartDate = '2011-07-01 00:00:00'

declare @EndDate datetime (null) = '2011-11-01 00:00:00'
set @EndDate = '2011-11-01 00:00:00'

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:37:17

quote:
Originally posted by sonjan

Hi
I would like to use a declare statement for a datetime variable - particulary between dates. This is the script I've written so far, which doesn't work:

declare @StartDate datetime (null)
set @StartDate = '2011-07-01 00:00:00'

declare @EndDate datetime (null)
set @EndDate = '2011-11-01 00:00:00'

select
workorder.wonum,
convert(varchar, workorder.reportdate, (103)) as report_date
from
workorder with (nolock)
inner join
failurereport with (nolock) on failurereport.wonum = workorder.wonum and failurereport.type = 'CAUSE' and failurereport.failurecode in ('C_GRAFF', 'C_VANDAL', 'C_THEFT', 'C_INCIDE', 'C_UNSEC')
where
workorder.reportdate between @StartDate and @EndDate

Error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'null'.
Msg 137, Level 15, State 1, Line 4
Must declare the variable '@StartDate'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'null'.
Msg 137, Level 15, State 1, Line 7
Must declare the variable '@EndDate'

Any assistance would be greatly appreciated. Do I need to convert the declare variables?
Thanks

make sure you specify length always when you cast/convert to varchar

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources