SSIS Date Expressions: 2 digit day and month.

Todd_Fields
Todd_Fields used Ask the Experts™
on
Hi,

I can't seem to figure out how to use the SSIS Expression syntax to ensure that the date is 8 digits for use in my file name (ex. 1333_Book2Book_20100129.txt).  The below only gets me 2010129.txt.

(DT_WSTR, 4) YEAR (GetDate() )  + (DT_WSTR, 2) Month (GetDate() )  + (DT_WSTR, 2) Day(GetDate() )

I have a function that I can call in SQL task to get a date in the right format, but I can't figure out how to save it to a variable.  I get an error with types mismatches.  I cast the value as varchar(8) and the variable is a string.

Any help would be much appreciated.

Thanks,
Todd
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
use this one

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD]
HainKurtSr. System Analyst

Commented:
check here for all other useful formattings...

http://www.sqlusa.com/bestpractices/datetimeconversion/

Author

Commented:
That still produces the error in the SQL Task that it is not in the format of my variable.  I've attached a screen shot of the variable and error.
SQL-to-Variable.jpg
SQL-Task.jpg
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HainKurtSr. System Analyst

Commented:
use

CONVERT(VARCHAR(10), GETDATE(), 112)

Author

Commented:
Hi Hain,

You had mentioned this convert SQL above.  If I don't use the keyword SELECT, it won't parse.  The screen shot, although cut off does have:

Select CONVERT(VARCHAR(10), GETDATE(), 112) as uRunDate

Let me know if I missed your response.

Thanks,
Todd
HainKurtSr. System Analyst

Commented:
looks good, now I guess you need to bind the value to your parameter
check here

http://www.sqlservercentral.com/Forums/Topic615235-148-1.aspx

Author

Commented:
I'll continue to work on this on Monday.  Thanks again.
Here is the document I put together for this subject.
Good Luck
HoggZilla

DynamicFlatFileName.pdf

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial