date problem in flat source

Question

date problem in flat source

kanoe82

Valued Member

Points: 70
More actions
February 21, 2013 at 4:13 am

#268899

Hello everyone,
So I really am a newbie and I have a flat source ( csv file ) with some dates. But all the dates don't have the same formats. For exemple :
01/12/2012 (dd/mm/yyyy)
03/Dec/2012 (dd/mmm/yyyy)
01/01/13 (dd/mm/yy)
I have to convert the dates to the same format ( dd-mm-yyyy) with SSIS but I really don't know how. I used a derived colum to change mmm into mm with a replace function. but I don't know if it's a good solution to my problem.
Any ideas please ?
Thank you for your time anyway 🙂

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

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

If all the input dates are valid dates in the format DMY then use a derived column as you stated but cast the input to date (proving your output table is expecting this)

Try something like

(DT_DBDATE)[Column 0]

in the expression for the derived column

Far away is close at hand in the images of elsewhere.
Anon.

Miles Neale SSChampion Points: 13147 More actions · Answer 2

Kanoe - did you get this working yet?

Not all gray hairs are Dinosaurs!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

If you import the data into a staging table, you could then execute:

DECLARE @D1 VARCHAR(20)

DECLARE @D2 VARCHAR(20)

DECLARE @D3 VARCHAR(20)

SET @D1 = '01/12/2012'

SET @D2 = '03/Dec/2012'

SET @D3 = '01/01/13'

SELECT CAST(@D1 AS DATE) AS 'was (dd/mm/yyyy)',CAST(@D2 AS DATE) AS 'was (dd/mmm/yyyy)' ,CAST(@D3 AS DATE) AS 'was (dd/mm/yy'

Result:

was (dd/mm/yyyy)was (dd/mmm/yyyy)was (dd/mm/yy)

2012-01-12 2012-12-03 2013-01-01

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

kanoe82 Valued Member Points: 70 More actions · Answer 4

Thanks all for your help unitl so far.

So, I tried your solution David but it didn't work. Or I didn't make it work I don't know :hehe:

This is a sample of the data in the source

04/01/2013 15:02

04/01/2013 15:42

21/Dec/12 2:17 PM

02/01/2013 14:30

21/Dec/12 1:04 PM

04/01/2013 13:46

21/Dec/12 5:46 PM

I don't know how to convert the hour, so still with a derived column, I just kept the date. Now I'm trying to convert it as dd/mm/yyyy...

@bitbucket-25253

I have a lot of datas, not juste 3, and I have to do an automatic task, so I don't think your solution is fine for this problem. But thank you 🙂

By the way, sorry for my poor english 🙂

Miles Neale SSChampion Points: 13147 More actions · Answer 5

If you want to include the time with the date use the DATETIME function in the CAST instead of DATE.

Also What is suggested is not to just convert the three dates used in the example but that you populate a temporary table and use that as input to cast datetime formats of the input to the common format you want.

Not all gray hairs are Dinosaurs!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

HERE it is from a table (Now I used a temporary table BUT ONLY for illustration IN your situation it would be the real table)

/* I used a temporary table BUT ONLY for illustration

the values I used are from your posting Posted Today @ 6:13 AM */

CREATE TABLE #T (D VARCHAR(20))

INSERT INTO #T(D)

VALUES ('01/12/2012'),('03/Dec/2012'),('01/01/13')

SELECT D as 'Input value', CAST(D AS DATE) AS 'Output' FROM #T

Results

Input value Output

01/12/2012 2012-01-12

03/Dec/2012 2012-12-03

01/01/13 2013-01-01

With your new data:

CREATE TABLE #T (D VARCHAR(20))

INSERT INTO #T(D)

VALUES ('01/12/2012'),('03/Dec/2012'),('01/01/13')

INSERT INTO #T(D)

VALUES ('04/01/2013 15:02'),('21/Dec/12 2:17 PM')

SELECT D as 'Input value', CAST(D AS DATETIME) AS 'Output' FROM #T

Results:

Input value Output

01/12/2012 2012-01-12 00:00:00.000

03/Dec/2012 2012-12-03 00:00:00.000

01/01/13 2013-01-01 00:00:00.000

04/01/2013 15:02 2013-04-01 15:02:00.000

21/Dec/12 2:17 PM 2012-12-21 14:17:00.000

I hope this assists you. If NOT please post why not and maybe someone can assist you further.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

kanoe82 Valued Member Points: 70 More actions · Answer 7

Thanks for your help.

I understand your solution, but I have 4 sources that I have to join, so there's a lot of datas, not just 3 or 4. With your solution, I have to do value by value ans that is not possible.

Is this possible to make it automatic after the extraction and before the loading?

kanoe82 Valued Member Points: 70 More actions · Answer 8

Guys, you are my heroes of the day. It now works.. so far, let's hope it keeps going like that !

Thank you very much, I will probably need your help again, so keep coming around sometimes 😛

Miles Neale SSChampion Points: 13147 More actions · Answer 9

bitbucket is the hero here, and well done with the example.

And by the way do not hesitate to ask questions as needed in the future. There is help here

M.

Not all gray hairs are Dinosaurs!