Not picking up @date...why? - 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 2000 Forums

Transact-SQL (2000)

Not picking up @date...why?

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-06-01 : 11:42:58

When I run the following code, it gives me this:

5
2005
May 1 2005 12:00AM
ODBC error 137 (42000) Must declare the variable '@date'.


declare @month int, @year int
declare @date datetime

set @month = datepart(month,getdate() - 1) 
set @year = datepart(year,getdate() - 1)
set @date = convert(varchar, @year) + right('0' + convert(varchar, @month), 2) + '01'

print @month
print @year
print @date 

exec master..xp_sendmail
@recipients = 'brenda@capitaltracer.com', 
@subject = 'Cases NOT Worked On Last Month',
@query = 'SELECT CaseNumber,LastName,Updated FROM tblCapRec WHERE Updated NOT BETWEEN dateadd( d, -1 , dateadd( m , 1 , @date ) ) - (datediff(d, @date, dateadd(m, 1, @date)) -1) AND dateadd( d, -1 , dateadd( m , 1 , @date )) AND (Status = 4)'

WHY?

Brenda

If it weren't for you guys, where would I be?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-01 : 11:48:00

@query = 'SELECT CaseNumber,LastName,Updated FROM tblCapRec WHERE Updated NOT BETWEEN dateadd( d, -1 , dateadd( m , 1 , ' + @date + ' ) ) - (datediff(d, ' + @date + ' , dateadd(m, 1, ' + @date + ' )) -1) AND dateadd( d, -1 , dateadd( m , 1 , ' + @date + ' )) AND (Status = 4)'

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
No Rows Returned

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 11:49:32

Because its outside of the "scope" and doesnt exist


@query = 'SELECT .... Updated NOT BETWEEN dateadd( d, -1 , dateadd( m , 1 , ' + @date + ' ) )......

Andy

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 11:52:31

Beauty is in the eyes of the beerholder

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-06-01 : 11:54:03

Now it gives me this error:

Line 15: Incorrect syntax near '+'.

It works when I do this though:


declare @month int, @year int, @date datetime

	set @month = datepart(month,getdate() - 1) 
	set @year = datepart(year,getdate() - 1)
	set @date = convert(varchar, @year) + right('0' + convert(varchar, @month), 2) + '01'

SELECT CaseNumber,LastName,Updated FROM crdatabase.dbo.tblCapRec WHERE updated NOT BETWEEN 
dateadd( d, -1 , dateadd( m , 1 , @date ) ) - (datediff(d, @date, dateadd(m, 1, @date)) -1)
AND dateadd( d, -1 , dateadd( m , 1 , @date )) AND Status = 4 --between first of the month 'til last day of the month

Brenda

If it weren't for you guys, where would I be?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-01 : 13:54:35

Yea, it does not like string evaluation inside the exec. sorry about that.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
No Rows Returned

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 14:03:39

Prepare @query outside of the EXEC.

DECLARE @SQL varchar(7000)

SET @SQL = 'SELECT ...

Then inside the EXEC: @query = @SQL

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-06-01 : 15:11:38

I have this now:


declare @month int, @year int
declare @date datetime
declare @sql varchar(7000)

set @month = datepart(month,getdate() - 1) 
set @year = datepart(year,getdate() - 1)
set @date = convert(varchar, @year) + right('0' + convert(varchar, @month), 2) + '01'
set @sql = 'SELECT CaseNumber,LastName,Updated FROM crdatabase.dbo.tblCapRec WHERE Updated NOT BETWEEN dateadd( d, -1 , dateadd( m , 1 , ' + @date + ') ) - (datediff(d, ' + @date + ', dateadd(m, 1,' + @date + ')) -1) AND dateadd( d, -1 , dateadd( m , 1 ,' + @date + ')) AND (Status = 4)'

exec master..xp_sendmail
@recipients = 'brenda@capitaltracer.com', 
@subject = 'Cases NOT Worked On Last Month',
@query = @sql

And I get this error now:

Syntax error converting datetime from character string.

I tried to change @date to varchar, but it doesn't work either...

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 15:16:34

You must convert @date to varchar each and every place you have it in @sql. Convert it to varchar(20).

If that seems repetitive, then declare @date as varchar(20) instead of datetime. Then you won't need to do the conversion in @sql.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-06-01 : 15:19:08

I changed it to varchar(20) and now it says this:

Arithmetic overflow error converting expression to data type datetime.

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 15:21:23

Could you do a PRINT @date for us after this line:

set @date = convert(varchar, @year) + right('0' + convert(varchar, @month), 2) + '01'

Keep @date as datetime, do the PRINT and show us what it displays.

BTW, always put a size on char and varchar:

set @date = convert(varchar(sizeGoesHere), @year) + right('0' + convert(varchar(sizeGoesHere), @month), 2) + '01'

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-06-01 : 15:23:21

It prints this:

May 1 2005 12:00AM

Brenda

If it weren't for you guys, where would I be?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-01 : 15:24:43

be sure to delimit your date with single quotes. Always print out the SQL string you are generating, never blindly execute a dynamic SQL string w/o first printing it out to the screen and examining to make sure it is well formed and what you intend.

IN your case, you are generating something like this:

SELECT 1/1/03 as SomeDate

But, of course, you want:

SELECT '1/1/03' as SomeDate

A quick review of the SQL generated would help you determine this.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 15:26:23

I am not able to reproduce your error. Check this out:


declare @month int, @year int
declare @date varchar(20)
declare @sql varchar(7000)

set @month = datepart(month,getdate() - 1) 
set @year = datepart(year,getdate() - 1)
set @date = convert(varchar, @year) + right('0' + convert(varchar, @month), 2) + '01'
set @sql = 'SELECT CaseNumber,LastName,Updated FROM crdatabase.dbo.tblCapRec WHERE Updated NOT BETWEEN dateadd( d, -1 , dateadd( m , 1 , ' + @date + ') ) - (datediff(d, ' + @date + ', dateadd(m, 1,' + @date + ')) -1) AND dateadd( d, -1 , dateadd( m , 1 ,' + @date + ')) AND (Status = 4)'

print @sql

Do you get an error with this? I don't. Check out the SELECT statement that was printed out. Notice that you don't have single quotes around the date.

Always PRINT @SQL before tring to EXEC it. Don't jump the gun and assume you have it formatted correctly. One step at a time. It'll make things much eaiser.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 15:27:48

by Jeff.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-06-01 : 15:30:47

Ok, I tried this:


declare @month int, @year int
declare @date datetime 
declare @sql varchar(7000)

set @month = datepart(month,getdate() - 1) 
set @year = datepart(year,getdate() - 1)
set @date = convert(varchar, @year) + right('0' + convert(varchar, @month), 2) + '01'
set @sql = 'SELECT CaseNumber,LastName,Updated 
		FROM crdatabase.dbo.tblCapRec 
		WHERE Updated NOT BETWEEN dateadd( d, -1 , dateadd( m , 1 , ' + @date + ') ) - 
		(datediff(d, ' + @date + ', dateadd(m, 1,' + @date + ')) -1) 
		AND dateadd( d, -1 , dateadd( m , 1 ,' + @date + ')) AND (Status = 4)'

print @sql

I get this:

Syntax error converting datetime from character string.

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 15:35:10

You still have @date as datetime and aren't converting it inside the @SQL. Please see my previous post for how to correct this.

You're also not adding single quotes around @date, so it won't work even after @date is fixed. But we can get to that after @date is fixed.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-06-01 : 15:40:04

I had changed it to varchar(20) but had changed it back to datetime to try stuff. Anyway, I changed it back to varchar(20) and then do print @sql and I get this:

SELECT CaseNumber,LastName,Updated
FROM crdatabase.dbo.tblCapRec
WHERE Updated NOT BETWEEN dateadd( d, -1 , dateadd( m , 1 , 20050501) ) - (datediff(d, 20050501, dateadd(m, 1,20050501)) -1)
AND dateadd( d, -1 , dateadd( m , 1 ,20050501)) AND (Status = 4)

And it gives me this error:

Arithmetic overflow error converting expression to data type datetime.

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 15:41:06

Post your code that is producing this error so that we can try it out on our machines. We can't see what you have changed to produce this error.

Notice the lack of single quotes around your dates though. See Jeff's post for details.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-06-01 : 15:43:09

declare @month int, @year int
declare @date varchar(20)
declare @sql varchar(7000)

set @month = datepart(month,getdate() - 1)
set @year = datepart(year,getdate() - 1)
set @date = convert(varchar, @year) + right('0' + convert(varchar, @month), 2) + '01'
set @sql = 'SELECT CaseNumber,LastName,Updated
FROM crdatabase.dbo.tblCapRec
WHERE Updated NOT BETWEEN dateadd( d, -1 , dateadd( m , 1 , ' + @date + ') ) -
(datediff(d, ' + @date + ', dateadd(m, 1,' + @date + ')) -1)
AND dateadd( d, -1 , dateadd( m , 1 ,' + @date + ')) AND (Status = 4)'
print @sql

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 15:44:50

Brenda,

When I run your code, I do not get an error.

Are you still running the EXEC? I'm sure that's where you are getting this error. Remember not to do that until @SQL is formatted correctly, which it isn't. You need to add single quotes around @date.

Tara

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources