• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 990
  • Last Modified:

Derived Column Transformation Editor - Format Date

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
MoreThanDoubled
Asked:
MoreThanDoubled
  • 6
  • 2
2 Solutions
 
MoreThanDoubledAuthor Commented:
I also noticed that with the expression i am using i somehow have two leading spaces when there should be none.
0
 
lcohanDatabase AnalystCommented:
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
 
MoreThanDoubledAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
lcohanDatabase AnalystCommented:
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
 
MoreThanDoubledAuthor Commented:
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
 
MoreThanDoubledAuthor Commented:
any updates/suggestions on this question?
0
 
MoreThanDoubledAuthor Commented:
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
 
MoreThanDoubledAuthor Commented:
My solution is the correct format in which i needed the data to be displayed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now