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 2008 Forums
 Transact-SQL (2008)
 Format day of year to 3 digits

Author  Topic 

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2013-01-22 : 10:31:21
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 statement
set @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 varchar
set @Time = 11
select 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 year
SELECT DATEPART(dy,GETDATE());
-- this gives you day of the year as a character string with zero padding
SELECT RIGHT('000'+CAST(DATEPART(dy,GETDATE()) AS VARCHAR(3)),3)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-22 : 10:56:51
see

http://visakhm.blogspot.in/2012/05/julian-to-iso-date-conversion-function.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2013-01-22 : 11:06:31
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
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 11:47:04
You are very welcome - glad to be of help.
Go to Top of Page
   

- Advertisement -