The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

Question

Boa Tarde Pessoal,<o:p></o:p>

Estou tentando o código abaixo e estou obtendo o seguinte erro:<o:p></o:p>

"The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value".

<o:p>Meu código:</o:p>

SELECT     RTRIM(dbo.USER_ADDRS.BNAME) AS LOGIN, dbo.USER_ADDRS.NAME_TEXT AS NOME, 'SAP' AS SISTEMA, dbo.USR02.TRDAT AS ULT_LOGON
FROM         dbo.USER_ADDRS INNER JOIN
                      dbo.USR02 ON dbo.USER_ADDRS.BNAME = dbo.USR02.BNAME
WHERE     (dbo.USR02.TRDAT <= GETDATE() - 45)<o:p></o:p>

O Formato do campo TRDAT é "20130927" e tem o formato nvarchar(24).

Pesquisei no fórum e testei algumas soluções com CAST e CONVERT, porém sem sucesso.

Poderiam me ajudar?

Abs

Erick

---

Erick Egea

Answer 1

Deleted

Answer 2

Bom dia José,

Realmente o campo TRDAT não formatados:

EX:

BNAME	TRDAT
7815COL025	00000000
7815COL024	00000000
7815COL026	00000000
7815COL023	00000000
40219854	00000000
09114599	00000000
40200653	00000000
09113835	00000000

Tentei usar o código abaixo:

SELECT    RTRIM(dbo.USER_ADDRS.BNAME) AS LOGIN, dbo.USER_ADDRS.NAME_TEXT AS NOME, 'SAP' AS SISTEMA, dbo.USR02.TRDAT AS Expr1
FROM         dbo.USER_ADDRS INNER JOIN
                      dbo.USR02 ON dbo.USER_ADDRS.BNAME = dbo.USR02.BNAME
WHERE     (CONVERT(date, dbo.USR02.TRDAT, 112) <= GETDATE() - 45)

Porém ele me retorna o erro:

Conversion failed when converting date and/or time from character string.

abs

Erick

---

Erick Egea

Answer 3

Consegui eliminando os valores = '00000000'.

SELECT     RTRIM(dbo.USER_ADDRS.BNAME) AS LOGIN, dbo.USER_ADDRS.NAME_TEXT AS NOME, 'SAP' AS SISTEMA, dbo.USR02.TRDAT AS Expr1
FROM         dbo.USER_ADDRS INNER JOIN
                      dbo.USR02 ON dbo.USER_ADDRS.BNAME = dbo.USR02.BNAME
WHERE     (NOT (dbo.USR02.CLASS IN ('BRZ-EXPIRED', 'DORMANT', 'MEX-EXPIRED', 'BRZ-COLETOR', 'MEX-SUSTAIN'))) AND (dbo.USR02.TRDAT <= GETDATE() - 45) AND
                      (dbo.USR02.TRDAT <> '00000000')

---

Erick Egea

Answer 4

Deleted