Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Derived Column Transformation Editor - Format Date

Posted on 2011-03-04
8
Medium Priority
?
950 Views
Last Modified: 2012-05-11
Hi,

I am using SSIS to create a flate file.
I have a file that consist of a header, detail and footer.  I am currently editing a date field using the derived column transformation editor (expression field).

Does anyone know what the best way would be to format my date so that it  looks like the following  YYYYMMDDHHMMSS.

I currently have the following but i am not sure how to add the "HHMMSS":
(DT_WSTR,4)(YEAR(GETDATE())) +""+ REPLICATE("0",2 - LEN((DT_WSTR,2)(MONTH(GETDATE())))) + (DT_WSTR,2)(MONTH(GETDATE())) +""+REPLICATE("0",2 - LEN((DT_WSTR,2)(DAY(GETDATE())))) + (DT_WSTR,2)(DAY(GETDATE())) +""+REPLICATE("0",2 - LEN((DT_WSTR,2)(DAY(GETDATE())))) + (DT_WSTR,2)(DAY(GETDATE()))


Thanks!
0
Comment
Question by:MoreThanDoubled
  • 6
  • 2
8 Comments
 

Author Comment

by:MoreThanDoubled
ID: 35039032
I also noticed that with the expression i am using i somehow have two leading spaces when there should be none.
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 2000 total points
ID: 35039303
Please try code below:

select CAST(YEAR(getdate()) AS VARCHAR(4))+
CAST(MONTH(getdate()) AS VARCHAR(2))+
CAST(DAY(getdate()) AS VARCHAR(2))+
CAST(DATEPART(hour, getdate()) AS VARCHAR(2))+
CAST(DATEPART(minute, getdate()) AS VARCHAR(2))+
CAST(DATEPART(second, getdate()) AS VARCHAR(2));
0
 

Author Comment

by:MoreThanDoubled
ID: 35039643
Icohon,

This is it possible to put a select statement in the "Expression Field" ? or are you assuming i am using a query for my header information.

See my attachment.  I am entering the functions in the derived column transformation editor.
SSIS.doc
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 40

Expert Comment

by:lcohan
ID: 35040207
You don't need the SELECT in the expresion fielrigt? I just gave the example how to build what you need to get YYYYMMDDHHMMSS and to show the results so you only need to put in the values:

CAST(YEAR(getdate()) AS VARCHAR(4))+
CAST(MONTH(getdate()) AS VARCHAR(2))+
CAST(DAY(getdate()) AS VARCHAR(2))+
CAST(DATEPART(hour, getdate()) AS VARCHAR(2))+
CAST(DATEPART(minute, getdate()) AS VARCHAR(2))+
CAST(DATEPART(second, getdate()) AS VARCHAR(2))
0
 

Author Comment

by:MoreThanDoubled
ID: 35058477
the query you gave me displays the following result
201137112759

20110307112759 is the result that i need.  I need my month and day to be displayed in a two digit format
0
 

Author Comment

by:MoreThanDoubled
ID: 35072631
any updates/suggestions on this question?
0
 

Accepted Solution

by:
MoreThanDoubled earned 0 total points
ID: 35074750
I used the following

SELECT      dtDate =
CONVERT(VARCHAR(8),GETDATE(),112),
dtHour = DATEPART(HOUR,GETDATE()),
dtMinute = DATEPART(MINUTE,GETDATE()),
dtSecond = DATEPART(SECOND,GETDATE())


lcohan you were close enough to the resolution.

0
 

Author Closing Comment

by:MoreThanDoubled
ID: 35120711
My solution is the correct format in which i needed the data to be displayed
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question