Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Derived Column Transformation Editor - Format Date

Posted on 2011-03-04
8
Medium Priority
?
962 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
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. …

577 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