Error converting dates during an insert

1

I need to load a table in one database with data from another table and another database. It turns out that in the DateTime field is giving a conversion error because the data from the other table is in Varchar. Here's how I did it:

use [Rup]
insert into [dbo].[Ruptura]
(
    DataRuptura ===>>> AQUI DÁ O ERRO
    ,[IDJustificativa]
    ,[IDOrigem]
    ,[CodigoPDV]
    ,[Codigo_Apresentacao]
)

select

Convert(DateTime,stg.DATA,103)==>> Tentativa e não deu certo
,mot.MOTNM_CODIGO AS RUPTURA
,1 AS ORIGEM
,pdv.PDVCH_CODIGO AS CODIGO_PDV
,apr.APRCH_COD_ACHE AS CODIGO_EAN
from [STG_Rup].[dbo].[RPT_STG_CARGA] stg
join [STG_Rup].[dbo].[RPT_CAT_MOTIVO_RUPTURA] mot on stg.JUSTIFICATIVA = mot.MOTCH_MOTIVO_RUPTURA
join [STG_Rup].[dbo].[MDTR_CAT_PDV] pdv on stg.CNPJ = pdv.PDVCH_CNPJ
join [STG_Rup].[dbo].[VDA_CAT_APRESENTACAO] apr on stg.EAN = apr.APRCH_COD_EAN

The stg.Data field is a varchar (100)

    
asked by anonymous 15.08.2014 / 13:55

2 answers

1

Resolved:

.....
select

Convert(DateTime,Substring(stg.DATA,0,10))==>> Aqui resolvi
,mot.MOTNM_CODIGO AS RUPTURA
,1 AS ORIGEM
,pdv.PDVCH_CODIGO AS CODIGO_PDV
,apr.APRCH_COD_ACHE AS CODIGO_EAN
from [STG_Ruptura].[dbo].[RPT_STG_CARGA_ABILITY] stg
left join [STG_Ruptura].[dbo].[RPT_CAT_MOTIVO_RUPTURA] mot on stg.JUSTIFICATIVA = mot.MOTCH_MOTIVO_RUPTURA
left join [STG_Ruptura].[dbo].[MDTR_CAT_PDV] pdv on stg.CNPJ = pdv.PDVCH_CNPJ
left join [STG_Ruptura].[dbo].[VDA_CAT_APRESENTACAO] apr on stg.EAN = apr.APRCH_COD_EAN
    
15.08.2014 / 14:44
0

Check the format of this string; some see this link

Yesterday I "handled this cart"; I passed a select with (@ "{0: MM / dd / yyyy HH: mm: ss}; then I changed it to {@" {0: yyyy-MM-dd hh: mm: ss}.

An "unequivocal" format would be YYYYMMDD, but I have not tested it.

Good luck, if you have not already.

    
15.08.2014 / 14:52