January 8, 2009 at 1:08 pm
I am looking to format the prior days date as follows. - 20090107
I tried the following and could not figure out how to keep the leading zero's
Select Cast(YEAR(DATEADD("dd", -1, getdate())) as nvarchar(4)) + Cast(MONTH(DATEADD("dd", -1, getdate())) AS nvarchar(2)) + Cast(DAY(DATEADD("dd", -1, getdate())) as nvarchar(2))
the result is 200917
Thnaks in advance for your help.
January 8, 2009 at 1:22 pm
Why not try
select convert(varchar(10), dateadd(dd, -1, getdate()), 112)
Edit Added Convert BOL REF
http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx
January 8, 2009 at 1:49 pm
That works although I neglected to mention that I was using SSIS expression builder and I need to build an expression that shows the leading 0's in the date. That is why I am using dateadd.
January 9, 2009 at 1:05 pm
In your expression Put
(DT_WSTR, 4) DATEPART( "Year", DATEADD("dd", -1, getdate())) + RIGHT("00" + (DT_WSTR, 4) DATEPART( "Month", DATEADD("dd", -1, getdate())),2) + RIGHT("00" + (DT_WSTR, 4) DATEPART( "Day", DATEADD("dd", -1, getdate())),2)
January 9, 2009 at 1:08 pm
That works great! Thank you very much for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply