SSIS - Script Component for Date Formatting - VB.NET/C#

0
votes

so I have a situation where (via SSIS) I'm basically running a SELECT against my DB, and then exporting the results to a .txt flatfile.

The SSIS flow is as follows: OLEDB SOURCE (my select query) -> DATA CONVERSION (change all values to DT_WSTR) -> FLAT FILE DESTINATION

However, I was told I need to change the format of all the datetime column values I export from the format of yyyy-mm-dd hh:nn:ss (what it is in the DB) to mm/dd/yyyy hh:nn:ss AM/PM

From my searching on this topic, I believe a script component between my DATA CONVERSION and FLAT FILE DESTINATION is what I need in SSIS....but I have no VB.NET or C# skills whatsoever. And despite searching, I have yet to really understand how I'd code this date formatting conversion up.

Help?

1
c#sqlvb.netssis

1 Answers

2
votes

You can do the conversion in your SELECT statement.

This will get your close: 

SELECT CONVERT(VARCHAR(20),GetDate(),101) + ' ' 
+ LTRIM(RIGHT(CONVERT(VARCHAR(26),GetDate(),9),14))

Just replace GetDate() with your date field. If you need it without the nanoseconds, it's a bit longer:

SELECT CONVERT(VARCHAR(20),GetDate(),101) + ' ' 
+ LTRIM(LEFT(RIGHT(CONVERT(VARCHAR(26),GetDate(),9),14),8)) + ' ' 
+ RIGHT(CONVERT(VARCHAR(26),GetDate(),9),2)