Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I found many articles on date formatting expressions in SSIS but I couldn't find one that specified how to get just the Year such as 13 for 2013 or 12 for 2012... I tried

DATAPART("yy",GETDATE())

but it gives me the same result as

DATAPART("yyyy",GETDATE())

I was thinking of a dirty/hack fix which would be something like

DATAPART("yyyy",GETDATE()) - 2000 

but it just doesn't feel right.... what if someone runs my package 1000 years from now? lol. Any suggestions?

I am trying to add a time stamp to a file name.. so today's file should be Auth040113.txt. I am creating the file name in a variable using expressions.

share|improve this question
    
Are you looking to do this as part of your source query or as an Expression in a component like a Derived Column –  billinkc Apr 1 '13 at 21:18
    
@billinkc it's actually in the Expression property for a Variable in the package –  JuanVelez Apr 1 '13 at 21:21
2  
Why are you doing this if it's just for a log file name? Use the ISO format YYYYMMDD and be done with it. There's almost no reason to use a 2-digit year for new development anymore. –  Jon Seigel Apr 1 '13 at 21:39
    
I know, But I am just trying to conform with the current naming convention that is already in place. –  JuanVelez Apr 1 '13 at 21:40
add comment

2 Answers

up vote 4 down vote accepted

Use the modulo operator %: http://technet.microsoft.com/en-us/library/ms141815(v=sql.100).aspx

DATEPART("yyyy",GETDATE()) % 100

Your next problem may be that this will still be be a number and thus will not have leading zeros.

What is your underlying problem? What are you trying to format a date as?

You would then just use (DT_WSTR, 2) DATEPART( "YY", GETDATE()) % 100 as part of your expression.

share|improve this answer
    
I am trying to add a time stamp to a file name.. so todays file should be Auth040113.txt. I am creating the file name in a variable using expressions. –  JuanVelez Apr 1 '13 at 21:23
    
That should work, Thanks! –  JuanVelez Apr 1 '13 at 21:41
add comment

I usually do my datatime formatting for file processing in a script task and save it as a variable to append to the filename.

For example:

        //Set date format for folder and file naming
        Dts.Variables["ProcessedDate"].Value = now.ToString("yyyyMMdd");

Then later I can just add the filename plus my processed date at the end.

But if need be, you could just take the 2 most right of the year after converting it to a string

RIGHT((DT_STR,4,1252)DATEPART("YYYY" ,GETDATE()), 2)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.