SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Format day of year to 3 digits		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kdeutsch
Yak Posting Veteran

USA
75 Posts
Posted - 01/22/2013 :  10:31:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1733 Posts
Posted - 01/22/2013 :  10:51:53  Show Profile  Reply with Quote
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

India
48076 Posts
Posted - 01/22/2013 :  10:56:51  Show Profile  Reply with Quote
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

USA
75 Posts
Posted - 01/22/2013 :  11:06:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1733 Posts
Posted - 01/22/2013 :  11:47:04  Show Profile  Reply with Quote
You are very welcome - glad to be of help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000