how to separate date & time during export

I would like to export sql server 2005 tables to a text file using SSIS. It is easy to do this as a direct export. However, how could I separate the datetime field into two fields: one for date only and one for time only?

Sample:

select getdate() ==> 2007-08-30 14:42:11.870

V

select convert(char, getdate(), 111) as date, convert(char, getdate(), 108) as [time]

==> Date = 2007/08/30

==> Time = 14:44:50

Others datetime format

century century
(yy) (yyyy) standard output format
- - --
- 0 or 100 (*) default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mms
- 9 or 109 (*) default mon dd yyyy
milliseconds hh:mis:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default dd mon yyyy
milliseconds hh:mms:mmm(24h)
14 114 - hh:mis:mmm(24h)

(*) the default values (style 0 or 100, 9 or 109, and
13 or 113) always return the century (yyyy)

|||

You have to use Derived Column Transformation of Data Flow.

So:

1. Add a Data Flow task to Control Flow window

2.Add OleDB Source then Derived Column and Finaly Flat File Destination linked with arrow

3. Double Click Derived Column and

write Derived Column Name desired then <add as new column> to Derived Column then in expresion : (DT_DBTIME)YourDataColumnName and Data Type database time[DT_DBTIME]

in second line write Derived Column Name desired then Replace 'YourDataColumnName' and in expression (DT_DBDATE)YourDataColumnName and Data Type databasedate[DT_DBDATE]

|||

I would use this expressions in a derived column:

For Date:

RIGHT("00" + (DT_WSTR,2)MONTH(MyDate),2) + "-" + RIGHT("00" + (DT_WSTR,2)DAY(MyDate),2) + "-" + (DT_WSTR,4)YEAR(MyDate)

For time:

(DT_DBTIME)MyDate