Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
I have a speciality number I need to create and need the day of year to always be in 3 digits, so I am attempting a Case statment but for anything other than a single digit I get the year 2 zeros and a *. what am i doing wrong.Original statementset @TJulian = Cast(Right(Year(getDate()),2) as varchar) + Case WHEN len(cast(datepart(dy, getdate())as varchar)) < 3 then '0' + cast(datepart(dy, getdate())as varchar) END Example trying to get to work.Declare @Time as varcharset @Time = 11select Cast(Right(Year(getDate()),2) as varchar) + Case WHEN len(@Time) = 3 then @Time WHEN len(@Time) = 2 then '0' + @Time WHEN len(@Time) = 1 then '00' + @Time ELSE @Time END
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-01-22 : 10:51:53
I didn't quite follow what you are trying to do, but may be this?
-- this gives you day of the yearSELECT DATEPART(dy,GETDATE());-- this gives you day of the year as a character string with zero paddingSELECT RIGHT('000'+CAST(DATEPART(dy,GETDATE()) AS VARCHAR(3)),3)
james k,Just what I was looking for, originally I solved it just not enough varchar added for the string to work, but yours in much simplete than mine. Thanks