convert getdate() to string dd-mm-yyyy

I would like to convert getdate() value to string of dd-mm-yyyy format in SSIS... how can I achieve this ?

Expression Date Functions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html)

RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"
+ (DT_WSTR,4)YEAR(GETDATE())

|||

Hmm... that is ugly clutter of functions!

I was hoping for a simple function [as in oracle to_char(date, format) ]

How come microsoft didn't provide such a simple and too commonly used function ?

|||

Probably because this is a version 1 product and the aim is to get in as much functionality as possible. Later I hope ti will be extended to include shorthand functions like you suggest. The syntax that Darren provided will work just fine.

Admittedly it would be nice to have shorthand functions that do this - or even better, the ability to build your own function libraries. if you think that would be a good idea then ask for it at Microsoft Connect.

It is inappropriate to compare SSIS's expression language to Oracle's PL/SQL language. If you want to compare apples with apples then you will find that the T-SQL convert() function is similar to PL/SQL's TO_CHAR() function.

-Jamie

|||If you wanna do it in a script task or component its just:

MyDate.ToString("dd-MM-yyyy")

HTH

PJ|||That works a treat, but it should come with information note that says it will be slower. I wouldn't worry about this for a task, but for a component, I would use the Derived Column over a Script Transform any day of the week, performance is much better. You have the choice however if you feel that the maintenance overhead of the more complicated expression syntax outweighs the performance loss.|||

It gets more complicated if I need to do any arithmetic on date… need to use dateadd function everywhere on getdate().... which may sometime lead to unnoticed/invisible errors..

I was trying to store current date minus 1 in a variable so the package extracts previous day’s data from source system for load.

I guess we have to leave with it for now….

Current Date minus 1:

RIGHT("0" + (DT_WSTR,2) DAY( DATEADD("DD",-1,GETDATE())), 2) + "-"

+ RIGHT("0" +(DT_WSTR,2) MONTH( DATEADD("DD",-1,GETDATE())), 2) + "-"+ (DT_WSTR,4) YEAR (DATEADD("DD",-1,GETDATE()))

|||

Have you registered anything at Microsoft Connect? If you don't ask you don't get.

-Jamie

|||

Jamie,

Added the suggestion in microsoft connect (subject "more standard functions needed in SSIS ").

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=166356

Request people to vote on this.

Thanks

|||

Mahesh,

Thanks for posting that!

I would actually rather they give us the ability to build user defined functions rather than adding a plethora of new out-of-the-box functions. I have added a comment to that end.

Regards

-Jamie

|||

Jamie Thomson wrote:

Mahesh,

Thanks for posting that!

I would actually rather they give us the ability to build user defined functions rather than adding a plethora of new out-of-the-box functions.

ask and ye shall receive: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=166367

|||

Bonzer!

Thanks Duane.

Although I think the phrase should be "ask and ye might one day get it if you're bloody lucky"

-Jamie

|||

I also think that the ability to reference a column created in the same Derived Column component would be damn useful: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127010

-Jamie