Using a @variable to select records in a openquery terradata file

Question

Using a @variable to select records in a openquery terradata file

cljolly

Ten Centuries

Points: 1179
More actions
August 15, 2012 at 1:52 pm

#265097

#tbl_deltab contains a date that is the last date of a finalized file. I have another file with data that I want to pull information by date that is later than the final date.
This is the query that I use for the singe value:select @lastdate1=lastdate
from #tbl_deltab
set @lastdate =CONVERT(VARCHAR(10), @lastdate1, 111)
The report runs fine when I have the date hard coded in the selection criteria as below:
cast (doc_date as date format ''''yyyy-mm-dd'''') >= ''''2012-08-01''''
What I want to do is to pull the data like this:
cast (doc_date as date format ''''yyyy-mm-dd'''') >= ''''' + @lastdate + '''''
I get this error: Msg 402, Level 16, State 1, Line 69
The data types varchar and date are incompatible in the add operator.
Aren't the selection date and the data dates in the same format?
Help. Thanks

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

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

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

cljolly (8/15/2012)
#tbl_deltab contains a date that is the last date of a finalized file. I have another file with data that I want to pull information by date that is later than the final date.
This is the query that I use for the singe value:select @lastdate1=lastdate
from #tbl_deltab
set @lastdate =CONVERT(VARCHAR(10), @lastdate1, 111)
The report runs fine when I have the date hard coded in the selection criteria as below:
cast (doc_date as date format ''''yyyy-mm-dd'''') >= ''''2012-08-01''''
What I want to do is to pull the data like this:
cast (doc_date as date format ''''yyyy-mm-dd'''') >= ''''' + @lastdate + '''''
I get this error: Msg 402, Level 16, State 1, Line 69
The data types varchar and date are incompatible in the add operator.
Aren't the selection date and the data dates in the same format?
Help. Thanks

How have you declared the variable @lastdate?

cljolly Ten Centuries Points: 1179 More actions · Answer 2

The table #tbl_deltab contains a single record and I use below to pull it:

select @lastdate1=lastdate

from #tbl_deltab

set @lastdate =CONVERT(VARCHAR(10), @lastdate1, 111)

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

cljolly (8/15/2012)
The table #tbl_deltab contains a single record and I use below to pull it:
select @lastdate1=lastdate
from #tbl_deltab
set @lastdate =CONVERT(VARCHAR(10), @lastdate1, 111)

Does not tell me how you have defined (declared) the variable @lastdate. Some where, some how, you had to declare this variable. My thoughts? You have declared as a datetime data type which means that the following statement:

set @lastdate =CONVERT(VARCHAR(10), @lastdate1, 111)

converts @lastdate1 to a varchar and then back to a datetime value.

This means you need to convert @lastdate back to a character string to concatenate it to another string.

cljolly Ten Centuries Points: 1179 More actions · Answer 4

My fault, I misunstood you. It is a date,null format

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

cljolly (8/15/2012)
My fault, I misunstood you. It is a date,null format

The rest of my comments above still stand.

You need to cast @lastdate back to a character string if you want to concatenate it to a string.