Variable in a select query

Hello I have the below:

DECLARE @RXBATCH1 VARCHAR(10),@RXBATCH2 VARCHAR(10)
SET @RXBATCH1 =(STR( DAY( GETDATE() ) )) + 'ADMT'
SET @RXBATCH2 = (STR( DAY( GETDATE()-1 ) ))+ 'ADMT'

Open in new window


how to write my select query such that

select * from rxs where rxbatch in(@rxbatch1)

Open in new window

Star79Asked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
DECLARE @RXBATCH1 VARCHAR(10),@RXBATCH2 VARCHAR(10)
SELECT @RXBATCH1 = CAST(DAY(GETDATE()) AS VARCHAR) + 'ADMT',
      @RXBATCH2 = CAST(DAY(GETDATE() - 1) AS VARCHAR) + 'ADMT'
0
 
xav056Commented:
select * from rxs where rxbatch in (SELECT @rxbatch1)
0
 
Brian CroweDatabase AdministratorCommented:
SELECT * FROM rxs WHERE rxbatch = @rxbatch1
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Star79Author Commented:
Both the above queries did not work. It did not return the data
0
 
Brian CroweDatabase AdministratorCommented:
Then you need to explain your process in more detail.  Based on the information you provided they should work.  Provide some sample data if possible.
0
 
xav056Commented:
If you aren't getting any results maybe your variable rxbatch1 is not what you think it is
after seting the variable
SET @RXBATCH1 =(STR( DAY( GETDATE() ) )) + 'ADMT'

do
SELECT @RXBATCH1
to see the value
0
 
Star79Author Commented:
Xav, very true the @rxbatch1 = 29 and not '29admt'

so the code
DECLARE @RXBATCH1 VARCHAR(10),@RXBATCH2 VARCHAR(10)
SET @RXBATCH1 =(STR( DAY( GETDATE() ) )) + 'ADMT'

Open in new window

is not concatenating 'ADMT' TO THE DAY
0
 
xav056Connect With a Mentor Commented:
SET @RXBATCH1 =CAST(DAY( GETDATE() ) AS varchar)  + 'ADMT'
0
 
Brian CroweDatabase AdministratorCommented:
After a little research I found that STR returns a string of length 10 with the value right-justified so when you add characters "ADMT" and then convert it to a VARCHAR(10) by assigning it to a variable of that type and length you are stripping the "ADMT" off.

There are numerous ways around this (CAST, LTRIM, increase variable size, etc.) but that is your root problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.