Problems with TRIGGER

I still have problems with my TRIGGER (T-SQL) to update a table. I will read some information from INSERTD table and create a String to execute EXEC(@.sSQL), I’m creating a dynamic SQL because the name of then table I’ll update is in the INSERTED table, the problem I have is that : I read a date from the INSERTED table (31/08/2007 12:15:30) and write this date into then update table, but the date change to this : 31/08/2007 12:16:00 above is part of the trigger that do this :

SELECT @.TipoConta = Tipo, @.Inscricao = Inscricao, @.MesAno = Mes_Ref, @.DataPagam = Data_Quitacao, @.ValorPago = Valor_Pago, @.LiberMult = Libera_Multa FROM INSERTED

SET @.YearTable = Right(@.MesAno, 4)

SET @.MyTable = 'LEITURAS_VALORES_' + @.YearTable

set @.sSQL =

'UPDATE ' +

@.MyTable +

' SET ' +

'VALOR_PAGO = ' + str(@.ValorPago, 12, 2) +

', DATA_SITUACAO = ''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + '''' +

', SITUACAO = '''+ @.Situacao + '''' +

', LIB_MULTA = ' + str(@.LiberMult, 2) +

' WHERE ' +

'INSCRICAO = ' + str(@.Inscricao, 8) +

'AND MES_ANO = ''' + @.AuxVar + ''''

exec (@.sSQL)

@.DataPagam is the date I’m reading from INSERTED

The problem is in the code : ', DATA_SITUACAO = ''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + '''' +

Date format (dd/mm/yyyy hh:mms) we use here in Brazil

Any one can help me,

Thanks, very much

Try something like this:

', DATA_SITUACAO = CAST(''' + Convert(char(10), @.DataPagam, 103) + ' ' + Convert(char(8), @.DataPagam, 114) + ''' AS DATETIME) '
|||

It could be that your installation is set to us_english which has a dateformat of MDY.

In your trigger, try setting SET DATEFORMAT DMY at the start.

HTH!

|||

I put ''' AS DATETIME) '

Error Source :

.NetSqlCliente Data Provider

Error Message :

Incorrect syntax near then keyword 'AS'

|||

I have already done it : SET DATEFORMAT DMY in my TRIGGER

Thanks