Solved

Derived Column Transformation Editor - Format Date

Posted on 2011-03-04
8
918 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 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