How to convert YYYYMMDD to datetime using SSIS Data Conversion?

Question

How to convert YYYYMMDD to datetime using SSIS Data Conversion?

pickgoods

SSC-Addicted

Points: 472
More actions
November 26, 2008 at 11:31 am

#127088

I have many *.csv files with a column value of YYYYMMDD. I need to import the string YYYYMMDD to a datetime column in a table. I expect to see the value in the datetime column in the table as "2008-06-03 00:00:00.000" for example.
I created a SSIS package and added Data Conversion between Flat File Source and OLE DB Destination on Data Flow designer. I have tried the all four Data Types related to date like [DT_DBDATE] ... to [DT_DATE] on Data Conversion Editor.
None of them are working but return error message saying:
"[Data Conversion [822]] Error: Data conversion failed while converting column "InvoiceDate" (35) to column "InvoiceDate" (835). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". "
If you execute the simple select statement "select convert(datetime, '20080603') as n", it will return
the result I want "2008-06-03 00:00:00.000". So, I'm sure that YYYYMMDD could be convertible to datetime format.
Do you know how to solve the converstion problem using SSIS?
Thank you for your expertise help.
Helpwanted

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

dave-dj SSChampion Points: 11297 More actions · Answer 1

SSIS is not based on T-SQL, so you can't use T-SQL staments in the expression editor.

I think to acheive the result you want, you will have to break apart the source date (YYYYMMDD) and contruct the date yourself

(you'll have to check the syntax on this, but hopely you'll get the gist of what its doing)

SUBSTRING(0,4, {sourceDate} ) + '-' + SUBSTRING(5,2, {sourceDate} ) + '-' + SUBSTRING(7,2, {sourceDate} ) + ' 00:00:00'

output that to a DT_DBTIMESTAMP data type.

let me know if that works

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

pickgoods SSC-Addicted Points: 472 More actions · Answer 2

Hi, Dave

Thank you for reply with suggestion. I understood that the T-SQL statement cannot be used on Data Conversion editor as the editor has no option to take it. Like I said the editor only provides 4 drop-menu options of DATE types. I have found a solution to do that using Derive Column Transformation instead of Data Conversion.

But Data Conversion should do as its name says ..... data type conversion from simple string YYYYMMDD to datetime, just like the result from the T-SQL statement.

I'm so curious why Data Conversion fails to do that. Anyway, thank you all of you. But I still want to hear from you if knows how to get the result from the string YYYYMMDD to "2008-06-03 00:00:00.000" using DATA Conversion?

Thank you!

HelpWanted

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

The data conversion is failing because there's bad data in that column somewhere in the file. You need to find out what the bad data/date is and fix it in the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Either that, or you're mapping doesn't actually equal what's in the file. Could be something stupid like a stray comma in some name in the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JustMarie SSCertifiable Points: 7771 More actions · Answer 5

Sorry to revive an old thread but I'm having the exact same problem and didn't see that a final 'Hey - this worked!' was posted.

I double-checked all my data and it's all in YYYYMMDD format.

I have the source set as a string.

I've tried breaking it out into MM-DD-YYYY and other permutatations.

In no way can I get this value to convert to a datetime.

One oddity here. I have the source set as a non-unicode string. When I tried to pull the year from the field I received an error that the YEAR function does not support data type of "DT_WSTR" and the source truly is set to be DT_STR.

Thoughts?

JustMarie SSCertifiable Points: 7771 More actions · Answer 6

OK - here's the short answer on how I did this.

Create a Derived Column transformation, create a new column, and put this in the expression field. Use your own field name, obviously.

(DT_DATE)(SUBSTRING([field],1,4) + "-" + SUBSTRING([field],5,2) + "-" + SUBSTRING([field],7,2))

Personally I name the derived column as CONV_[field] just so I know it's a converted field and what field I converted. Use whatever naming convention you like.

Use the derived column in your destination mapping.

Longer answer.

Make sure you have your source coming in as a [DT_STR] data type.

Even longer answer.

If you're setting this up for the first time and creating the table, here's the way I found it most convenient to get things correct in the long run.

When creating your flat file data connection set the data type to [DT_DBTIMESTAMP] for all the fields that have the YYYYMMDD format. After you get all the fields properly formatted create an OLE DB destination and connect the two. Make sure you have your OLE DB connection manager in place. Select the connection manager and next to the field for 'Name of the table or the view' click on the New button. You'll get a window with the code to create the table.

Personally, I copy this code and bring it over to the Management Studio and put it in a query window. Put in your schema, desired table name, add the values for your decimal fields, etc. Run the query to create the table.

Now go back and edit the flat file connection manager. Change all the date fields to [DT_STR] and change the output column width value to match the input column width, as the value will default to 50 when you change the data type.

Disconnect the source from the destination and then put your transformations in place. Convert the date values as above and when you finally do connect the destination change your mapping to the converted/derived column.

Hope this helps.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Heh... thanks for posting your solution. I guess that's why I don't like DTS or SSIS... T-SQL makes it so much simpler...

DECLARE @SomeDate VARCHAR(8)

SET @SomeDate = '20090311'

SELECT CAST(@SomeDate AS DATETIME) AS DateTimeDataType

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

yinchashe SSC Enthusiast Points: 175 More actions · Answer 8

yinchashe

SSC Enthusiast

Points: 175

August 24, 2010 at 11:12 pm

#1211451

thanks MrsPost!!! that works

yinchashe SSC Enthusiast Points: 175 More actions · Answer 9

yinchashe

SSC Enthusiast

Points: 175

August 24, 2010 at 11:14 pm

#1211452

thanks MrsPost!!! that works

Just Another Developer Valued Member Points: 55 More actions · Answer 10

I had the same problem but with other datatypes as well (GUID, timestamp, int, datetime, ect).

Source of the problem:-

What I found is that when you define your datatype under the "Flat file Connection Manager Editor" "Advanced" section as the actual datatype, SSIS does not convert it. It instead give the following Error:-

"Error: 0xC02020A1 at Data Flow Task, Flat File Source [20490]: Data conversion failed. The data conversion for column "Column1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."."

I assue this is because it is because it is exspecting the correctly formated data but the data is actualy a string and may be formted slightly diffrently or even have a differnent precision.

EG1 GUID's have {}

EG2 Dates are exported with 9 frational seconds but SSIS DT_Date only accepts 7

The resolution I used is:-

When you define your datatypes for your flat file in the "Flat file Connection Manager Editor" "Advanced" section set the type as string DT_STR.

Your "Flat File Source" will now show the column as a string and the data path Metadata will also be string. It will now still give truncation error but later in the dataflow and it seems to be able to handle the error at this point.

You can then use "Data conversion" "Data Flow Transfermation" task.

And final an SQL Destination

cw-richard.reeder Valued Member Points: 55 More actions · Answer 11

(DT_DBDATE)(SUBSTRING(ActualShipDate,1,4) + "-" + SUBSTRING(ActualShipDate,6,2) + "-" + SUBSTRING(ActualShipDate,9,2))

This is for a date that comes in the following format

2011.01.13 00:00:00

It gets converted to DataType_DBDATE

2011-01-13

Libby Ten Centuries Points: 1175 More actions · Answer 12

Thanks JustMarie for being so thorough in your solution! It sure helped me out.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

I'm try to get yesterdays Date in an SSIS Expression and I keep getting syntax errors.

With all of the people that have subscribed to this post I thought that someone mayknow the answer.

@[Dest_Dir_Deploy] + @[Dest_File] + "_"

+

(DT_WSTR,4)DatePart("yyyy", GetDate()) + "_"

+

RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2)+ "_"

+

RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) + ".xlsm"

Evaluates to path & File name with Todays Date:

C:\SSIS\TXQuoteActivity\Deployment\FormattedTXQuotes_2011_11_10.xlsm

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

crosby_grant SSC Journeyman Points: 78 More actions · Answer 14

The cast you are attempting is legal.

You can go from strings to dates and vice-versa.

However, SSIS is looking for the format "YYYY-MM-DD" (note the hyphens).

Try the following Expressions in the Transform of your choice (more in a moment):

GENERATES ERROR: (DT_DBDATE)"20110322"

WORKS AS ESPECTED: (DT_DBDATE)"2011-03-22"

So, one suggestion might be to insert the hyphens into your pretty YYYYMMDD to get the YYYY-MM-DD that SSIS is looking for, like this (substitute your own "Date" expression):

(DT_DBDATE)(SUBSTRING((DT_WSTR,8)Date,1,4) + "-"+ SUBSTRING((DT_WSTR,8)Date,5,2) + "-" + SUBSTRING((DT_WSTR,8)Date,7,2))

Similar, but using richard.reeder's style, from above:

(DT_DBDATE)(SUBSTRING(Date,1,4) + "-" + SUBSTRING(Date,5,2) + "-" + SUBSTRING(Date,7,2))

Another suggestion might be to use one of the regular expression transforms, with something like the following pseudo/untested code:

Regular Expression Replace: (Date,"(?<YYYY>[0-9]{4})(?<MM>[0-9]{2})(?<DD>:[0-9]{2})","{YYYY}-{MM}-{DD})

Quick note on transforms: Derived vs. Conversion:

In general, used derived transforms, even when you are simply converting something. Personally, I like to replace my existing column data (no need to keep pushing the "bad" data, nor meta-data, around). Derived Transforms also support cascading and dealing with various inputs more gracefully. I like to think of Conversion transforms as the simpleton, and Derived as the more grown-up, fully featured solution to the same problem.

Resources: