Date Conversion in SSIS

Question

Date Conversion in SSIS

Anamika

SSCertifiable

Points: 6202
More actions
June 23, 2009 at 8:45 pm

#132188

Hi All,
I have a string and i want to convert that to a date in SSIS.
the string is in ddmmyy format. if I use the date conversion DT_DBDATE, it converts the value into mmddyy format. for example if i give '25/06/02' it converts into correct value (25th June. but if I give '03/02/09' then it converts the value into March 2nd and not Feb.3rd which i want. is there any way to set the dateformat as dmy as we do in SQL Server?
Thanks,
Regards
Viji

Viewing 11 posts - 1 through 10 (of 10 total)

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

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 1

Can you write a function in SQL that takes string as input and outputs date and then apply that function in the select of your source.... - a solution. (may not be the best).

Apply custom funtion\method in DTS script task to take input row and transform it.

thepotplants Hall of Fame Points: 3079 More actions · Answer 2

I had the same problem and was given these suggestions:

http://www.sqlservercentral.com/Forums/Topic517984-148-1.aspx

The short answer is: there is no easy way to do this in SSIS. As in you can't specify or force interpretation of a string/date as you can in TSQL.

The cheap and cheerful way around this is (if possible) change the date format in the file.

I beleive this is a ridiculous oversight in SSIS. This is a piece of cake in Webmethods and Tibco.

Anamika SSCertifiable Points: 6202 More actions · Answer 3

Thanx for your reply.

but my problem is if it is 14/05/09 it takes as 14th May 2009.

But if it is 04/15/09 then it takes 15th April 2009

but i have to send this date as a error output as it should accept only ddmmyy format.

any help on this is highly appreciated.

or can't we do such things in SSIS??

regards

viji

thepotplants Hall of Fame Points: 3079 More actions · Answer 4

viji (6/24/2009)
but my problem is if it is 14/05/09 it takes as 14th May 2009.
But if it is 04/15/09 then it takes 15th April 2009
viji

Well... I think your example above is bad, because it is interpreting each date correctly. But I think I know what you mean. I had a similar problem. I had a feed file with dates in dd/mm/yyyy format. But SSIS would interpret them with a default foramt of mm/dd/yyyy.

02/03/2009 would be mis-interpreted as 3-Feb-2009

03/03/2009 would be mis-interpreted as 3-Mar-2009

04/03/2009 would be mis-interpreted as 3-Apr-2009

etc

12/03/2009 would be mis-interpreted as 3-Dec-2009

until SSIS could no longer logically interpret them as mm/dd/yyyy. Then it would then start reading as dd/mm/yyyy

13/03/2009 would be correctly interpreted as 13-Mar-2009

14/03/2009 would be correctly interpreted as 14-Mar-2009

It would happily alternate interpretation throughout a file.

I gave up trying to code a solution, and instead had the date format changed in my feed file to be mm/dd/yyyy.

Anamika SSCertifiable Points: 6202 More actions · Answer 5

what you are telling is 100% true.

But my client wants to send an error message with only those records if it is not in proper dd/mm/yy format.

really held up.

-Viji

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 6

I'm pretty sure you could accomplish this with the Data Flow Script component. You would have VB.Net at your disposal to interpret or manipulate the date string and direct the row to different Outputs.

Anamika SSCertifiable Points: 6202 More actions · Answer 7

Hi,

i'm from data base background. so i don't knw much abt . net

but if u say that is the only solution, then i can take someone's help from my team.

thanks,

regards

viji

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 8

You can use an expression for this.... rather than leave SSIS to work it out implicitly, you can explicity set it out as a date using ANSI date format. i.e. YYYY-MM-DD. In other words manipulate your string into this format before you try to convert it into a date time. This will ensure there is no room for misinterpretation.

Expression would look something like (DB_DATE)(SUBSTRING(MyColumn, 6,4) + '-' + SUBSTRING(MyColumn, 4,2) + '-' + SUBSTRING(MyColumn, 1,2) )

This expression is off the top of my head so may not be exact, but basically use the expression substring function to chop the numbers out of the string and manipulate them into the standard format.

Also, as you're using a 2 digit year, you may need to perform some further logic to turn it into a 4 digit year.

HTH

Kindest Regards,

Frank Bazan

gmcdas SSC Rookie Points: 27 More actions · Answer 9

Hi,

The below code in VB.net will do the conversion.

Dim myDate As String = "2009/06/30"

' initialise it with the format you have

Dim myconvDate As Date = Date.Parse(myDate)

'This converts the date from string. Date should be valid else you need to put try and catch.

'SSIS tasks will be much simpler once you leverage it wth vb.net

Good Luck!

Gokul

bjlynch SSC Rookie Points: 27 More actions · Answer 10

Hi,

It might be late to post a reply but hopefully someone will get some benefit. I have had the same problem of dates being misinperpreted by being read in as dd/mm/yy and mm/dd/yy.

I am staging the data as text after reading it in from a texrt file and so I have added in an extra sql that runs after the data is staged to reformat the dates from from mm-dd-yyyy to yyyy-mm-dd which will then work when converted to a real date later on.

-- code for sql task starts here

set dateformat mdy

update staging_table set weekEnding = left(convert(date,weekEnding,101),10)

set dateformat dmy

-- code for sql task ends here

Brendan