CODEWITHSUNDEEP
sqlsql-server
SOK
SOK

Reputation: 1792

Insert and converting date columns into different SQL Server table

I have the following StagingTable in my SQL Server database:

     Date1               Date2
2020-10-19 04:00    20-10-18 16:42:32

These are both setup as varchar(max) column types.

I am trying to send them over to my main Table which are setup as datetime2(7) column types.

I have used the following code:

INSERT INTO [dbo].[Table]
    SELECT 
        CONVERT(datetime2, [Date1]),
        CONVERT(datetime2, [Date2])
    FROM 
        [dbo].[StagingTable]

but when I run the query I get the following error:

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

Any ideas on how to get around this and effectively insert across? I have done this before and usually the `convert function works. Any help would be much appreciated!

Upvotes: 1

Views: 137

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Hmmm. Your second value has a two digit year, which is an issue. You might try:

coalesce(try_convert(datetime2(7), date1),
         try_convert(datetime2(7), '20' + date1)
        ),
coalesce(try_convert(datetime2(7), date2),
         try_convert(datetime2(7), '20' + date2)
        )

Presumably, the two-digit year could appear in either column.

Here is a db<>fiddle.

Upvotes: 1

ali azizan
ali azizan

Reputation: 311

try chance this

INSERT INTO [dbo].[Table]
SELECT convert(datetime2,[Date1],101)
  ,convert(datetime2,[Date2],2)

 FROM [dbo].[StagingTable]

Upvotes: 0

Related Questions