2

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.

4
  • 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, 2013 at 21:18
  • @billinkc it's actually in the Expression property for a Variable in the package
    – Juan Velez
    Apr 1, 2013 at 21:21
  • 3
    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, 2013 at 21:39
  • I know, But I am just trying to conform with the current naming convention that is already in place.
    – Juan Velez
    Apr 1, 2013 at 21:40

3 Answers 3

4

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.

1
  • 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.
    – Juan Velez
    Apr 1, 2013 at 21:23
1

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)

0

Tried the mod but it didn't work for me. However, this worked:

substring( (DT_WSTR,4)DATEPART("YY",GetDate()), 3, 2 ) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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