Solved

Derived Column Transformation Editor - Format Date

Posted on 2011-03-04
8
905 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 39

Assisted Solution

by:lcohan
lcohan earned 500 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
 
LVL 39

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

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 …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now