Showing posts with label declare. Show all posts
Showing posts with label declare. Show all posts

Thursday, March 29, 2012

Concatenate a string problem

Hi to all:

In my package, I have in OLE DB SOURCE a statement:

DECLARE @.CMonth as smalldatetime

SET @.CMonth = '11/1/2006'

select day(@.CMonth)+month(@.CMonth)+year(@.CMonth)as ID_Month

and in OLE DB DESTINATION, I have ID_Month column as a char(10).

I want to be result as concatenated string – 1112006, but I receive 2018 (As a result of calculation)

Can anybody help me? Thank you

DECLARE @.CMonth as smalldatetime

SET @.CMonth = '11/1/2006'

select cast(day(@.CMonth) as varchar(2))+cast(month(@.CMonth) as varchar(2))+cast(year(@.CMonth) as varchar(4)) as ID_Month

|||Thank you very much Attila Soos. It works great!

Wednesday, March 7, 2012

Complex SP Issues (REPLACE and Transactions)

EDIT: Most of this thread was self-solved. The result is:

To REPLACE with an SP variable, use this syntax:


DECLARE @.SQL AS VARCHAR(4000)

SELECT @.SQL = REPLACE('A','B','C')


The only outstanding question from the original thread content is:

When using EXEC, does it remain within the scope of a TRANS?

BEGIN
BEGIN TRAN T1
SET @.SQL = 'Some SQL statement'
EXEC (@.SQL)
COMMIT TRAN T1
END
GOI fixed the sysmessages table error. Just a simple transposition of 'master.dbo.sysmessages'. And it appears the "Incorrect syntax near '<' is a result of the failed REPLACEs.