0

I am trying to convert a string into Date using Derived column and I am using Flat File Source. I read bunch of articles on stackoverflow and followed the same steps but I am unable to convert my string into date format.

Here is the string

Tue Nov 25 17:32:03 EST 2008

And I want to show it like

2008-nov-25

I tried using the following code

(DT_DBDATE)(SUBSTRING(dateCreated,24,4) + "-" + SUBSTRING(dateCreated,4,3) + "-" +    SUBSTRING(dateCreated,8,2))

I also tried to use 

  (DT_DATE)(SUBSTRING(dateCreated,24,4) + "-" + SUBSTRING(dateCreated,4,3) + "-" +    SUBSTRING(dateCreated,8,2))

  (DT_DBTIMESTAMP)(SUBSTRING(dateCreated,24,4) + "-" + SUBSTRING(dateCreated,4,3) + "-" +    SUBSTRING(dateCreated,8,2))

Is this the correct way to convert the string into date format?

asquare
  • 77
  • 1
  • 3
  • 11

2 Answers2

0

I dont think its possible to display the in the format of yyyy-mon-dd as per BOL. I checked your expression and after minor changes, was able to make it to work. You cannot typecast your string into a string again (You want month name in your output). Even if you were to use the DATEPART function, it would again amount to concatenating different parts into a string.

The expression that works with your input is as follows (replace @dateCreated with whatever you want to).

SUBSTRING(@[User::dateCreated],24,5) + "-" + SUBSTRING(@[User::dateCreated],4,4) + "-" + SUBSTRING(@[User::dateCreated],8,3)
rvphx
  • 2,324
  • 6
  • 40
  • 69
  • Thanks it worked fine. I am able to save the dates in destination table.But my column has datetime datatype. So it is now saving the as 2008-11-25 00:00:00.000. So Can I use the SUBSTRING to get time as well from my string? So that the final output is like 2008-11-25 17:32:03 – asquare Jul 12 '12 at 20:32
  • Yes. You can very well do that. If your destination is DATETIME field, cast the above expression completely to a DB_DATETIME. – rvphx Jul 12 '12 at 20:51
  • I think there is no typecasting function like DB_DATETIME in derived column Type Casts list. – asquare Jul 12 '12 at 21:13
  • Sorry I was my mistake it is working now. Thanks for your help @Rajiv Varma – asquare Jul 12 '12 at 21:23
  • Thanks Rajiv! So The final expression which gives the desired result is:- (DT_DBTIMESTAMP)(SUBSTRING(dateCreated,24,5) + "-" + SUBSTRING(dateCreated,4,4) + "-" + SUBSTRING(dateCreated,8,3) + " " + SUBSTRING(dateCreated,11,9)) The input being :- Tue Nov 25 17:32:03 EST 2008 And the output is:- 2008-11-25 17:47:41.000 – asquare Jul 12 '12 at 21:27
  • Awesome!! Keep posting more questions ;) – rvphx Jul 12 '12 at 21:30
0

Thanks Rajiv!

So The final expression which gives the desired result is:-

(DT_DBTIMESTAMP)(SUBSTRING(dateCreated,24,5) + "-" + SUBSTRING(dateCreated,4,4) + "-" + SUBSTRING(dateCreated,8,3) + " " + SUBSTRING(dateCreated,11,9))

The input being :-

Tue Nov 25 17:47:41 EST 2008

And the output is:-

2008-11-25 17:47:41.000
asquare
  • 77
  • 1
  • 3
  • 11