Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

date problem in flat source Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 4:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 10, 2013 6:02 PM
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 :)
Post #1422494
Posted Thursday, February 21, 2013 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 6,917, Visits: 6,988
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.

Post #1422587
Posted Thursday, February 21, 2013 10:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:08 PM
Points: 2,356, Visits: 1,427
Kanoe - did you get this working yet?

Not all gray hairs are Dinosaurs!
Post #1422705
Posted Thursday, February 21, 2013 11:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:17 PM
Points: 5,332, Visits: 25,261
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
Post #1422723
Posted Thursday, February 21, 2013 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 10, 2013 6:02 PM
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

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 :)
Post #1422747
Posted Thursday, February 21, 2013 12:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:08 PM
Points: 2,356, Visits: 1,427
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!
Post #1422757
Posted Thursday, February 21, 2013 1:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:17 PM
Points: 5,332, Visits: 25,261
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
Post #1422763
Posted Thursday, February 21, 2013 1:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 10, 2013 6:02 PM
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?
Post #1422768
Posted Thursday, February 21, 2013 1:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 10, 2013 6:02 PM
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

Post #1422782
Posted Thursday, February 21, 2013 2:05 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:08 PM
Points: 2,356, Visits: 1,427
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!
Post #1422787
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse