asked on

SSIS Date Expressions: 2 digit day and month.

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

HainKurt

use this one

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD]

HainKurt

check here for all other useful formattings...

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

Todd_Fields

ASKER

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

HainKurt

use

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

Todd_Fields

ASKER

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

HainKurt

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

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Todd_Fields

ASKER

I'll continue to work on this on Monday. Thanks again.

Steve Hogg

Here is the document I put together for this subject.
Good Luck
HoggZilla

DynamicFlatFileName.pdf