SSIS Expression Builder - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS Expression Builder

Author

Topic

fullyii
Starting Member

5 Posts

Posted - 2009-01-08 : 15:56:15

I am looking to format the prior days date as follows is SSIS exp builder. - 20090107

I tried the following and could not figure out how to keep the leading zero's


@[User::filepath] + "ADNDO335"  + "_" +  (DT_STR,4,1252)YEAR(DATEADD("dd", -1, getdate())) + (DT_STR,4,1252)MONTH(DATEADD("dd", -1, getdate())) + (DT_STR,4,1252)DAY(DATEADD("dd", -1, getdate())) + ".txt"

the result is 200917

Thanks in advance for your help.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-09 : 05:00:42

Using TSQL to get yesterday as varchar keeping zeros:

declare @Yesterday datetime
declare @sYest varchar (30)
set @Yesterday = getdate()-1
set @sYest = (select Convert(varchar,Year(@Yesterday)) + Convert(varchar,Month(@Yesterday))+ Convert(varchar,Day(@Yesterday)))

select @sYest

revelator
Starting Member

32 Posts

Posted - 2009-01-09 : 06:29:32

No idea if this will work, but could you try something like this in your expression?

(DT_STR,4,1252)RIGHT("00"+MONTH(DATEADD("dd", -1, getdate())),2)

Waiting for the Great Leap Forwards

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-01-09 : 06:29:55

add RIGHT():


@[User::filepath] + "ADNDO335"  + "_" +  (DT_STR,4,1252)YEAR(DATEADD("dd", -1, getdate())) + RIGHT("0" + (DT_STR,4,1252)MONTH(DATEADD("dd", -1, getdate())),2) + RIGHT("0" + (DT_STR,4,1252)DAY(DATEADD("dd", -1, getdate())),2) + ".txt"

Result:
ADNDO335_20090108.txt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources