SSIS to SQL Server data import and conversion issue

Question

I am a beginner with SQL Server 2014. I need some advise with data type conversion from SSIS to SQL Server.

I am working with a DATETIME column, an example value is "2017-08-17 16:00:50" which is in format 'YYYYMMDDHHMMSSsss'

From SSIS I have used data type Unicode string [DT_WSTR] and in SQL Server I have used data type NVARCHAR.

When I am trying to import the data from SSIS, it changes the date format from 2017-08-17 16:00:50 to 17/08/2017 4:00:50 PM. How do I prevent this and also when I try and convert the varchar date to datetime format using a cast or convert like this:

With convert

select 
    convert(datetime, [LastUpdateDateTime], 109) as [LastUpdateDateTime]
from 
    mytable

I get the this error:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

With a cast

select 
    cast([LastUpdateDateTime] as datetime) as [LastUpdateDateTime]
from 
    mytable

I get the this error:

Msg 242, Level 16, State 3, Line 2
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Please advise,

Where do you see "17/08/2017 4:00:50 PM "? In ths SSIS viewer? I n a preview window? in the database? are you loading it into a datetime field in the database? True dates don't have formats. It's just the tool that you're looking at will impose a format on it. Never use cast to convert a date. Use convert or try_convert, along with a datetime code from here docs.microsoft.com/en-us/sql/t-sql/functions/…. Never load a date into a varchar field. Always load it into a date or datetime field — Nick.McDermaid
If you have a DATETIME - leave it as DATETIME, don't convert / store it as NVARCHAR !! Also: DATETIME in SQL Server has no format since it's an 8-byte binary value that is being stored. The formatting only happens when you need to display the value - and you can handle that any way you like. — marc_s

Saeed Esmaeelinejad Saeed Esmaeelinejad · Accepted Answer · 2017-08-18T11:44:02

You should use date style 103 in convert
try this:

Declare @dt nvarchar(50)=CONVERT(nvarchar(50),[LastUpdateDateTime],103)
SELECT CONVERT(DATETIME2(0), @dt, 103)

SSIS to SQL Server data import and conversion issue

1 Answers