CODEWITHSUNDEEP
sql-serversql-server-2008ssis
343
343

Reputation: 305

Date Format In SSIS Derived Column

I have 2 questions, I have a text file with all my data, in the text file I have for example Sply_DT and Imprt_DT.

For Sply_Dt I have to create getdate() and I have it formatted as 2012-10-25 12:04:16.09900000 using (DT_DBTIMESTAMP)(DT_DBDATE)GETDATE() but I want it formatted as MM-DD-YY.

And for Impt_DT, it's in the 5/16/2011 format in dataviewer but when I placed it into a table it looks like 2011-05-16 00:00:00.000 and I want it in MM-DD-YY format.

Upvotes: 1

Views: 6129

Answers (3)

ajdams
ajdams

Reputation: 2314

An easier way to do it using the Derived Column component is simply like the following (for MM-DD-YY format):

LEN((DT_WSTR, 2)MONTH(GETDATE())) == 1 ? "0" + (DT_WSTR, 2)MONTH(GETDATE())) : (DT_WSTR, 2)MONTH(GETDATE())) + "-" + LEN((DT_WSTR, 2)DAY(GETDATE())) == 1 ? "0" + (DT_WSTR, 2)DAY(GETDATE())) : (DT_WSTR, 2)DAY(GETDATE())) + "-" + RIGHT((DT_WSTR, 2)YEAR(GETDATE()), 2)

Upvotes: 1

billinkc
billinkc

Reputation: 61249

I think you have some confusion about the datetime data type. It does not care whether your locale is US (mm/dd/yyyy), Japan (yy/mm/dd) or the UK (dd/mm/yyyy), it will always be stored in the internal format.

If you don't like the default presentation, you can investigate SET DATEFORMAT and perhaps that makes sense for your query.

You can also apply the appropriate CONVERT format when you are querying the data to make it in your intended format.

DECLARE @datevar datetime = '2012-10-25'
SELECT CONVERT(char(10), @datevar, 10) AS YourFomat, @datevar AS defaultFormat

If I have misunderstood your question, please clarify.

Upvotes: 3

CodeMonkey1313
CodeMonkey1313

Reputation: 16031

As I understand it you're aiming to alter the datetime format of data coming from the text file. I recommend you use a derived column transform in a data flow task to either add a column or replace the existing column, then you can use more common .NET date operators and format strings within the derived column to first parse the date, then to convert it to a string with the given format. If that does not work, you can instead use a script component in the data flow task to do what I described, in which case you have access to .NET to perform your modifications.

Upvotes: -1

Related Questions