SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


date problem in flat source


date problem in flat source

Author
Message
kanoe82
kanoe82
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 21
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 Smile
David Burrows
David Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16259 Visits: 10109
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
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4198 Visits: 1695
Kanoe - did you get this working yet?

Not all gray hairs are Dinosaurs!
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15203 Visits: 25280
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

Before posting a performance problem please read
kanoe82
kanoe82
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 21
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 Smile

By the way, sorry for my poor english Smile
Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4198 Visits: 1695
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!
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15203 Visits: 25280
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

Before posting a performance problem please read
kanoe82
kanoe82
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 21
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
kanoe82
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 21
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 :-P
Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4198 Visits: 1695
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search