How to prevent the truncation of the leading zero in a varchar string?

Question

Post reply

How to prevent the truncation of the leading zero in a varchar string?

MothInTheMachine

Hall of Fame

Points: 3251
More actions
November 7, 2010 at 1:03 pm

#242686

I am working on a function that will return the military julian time in the format of YYDDD, where YY is the last 2 digits of the year and DDD is the ordinal day of the year.
When I pass the function a date like '11/7/2008' the function should return '08312' but instead it returns '8312'
I defined my return type as varchar(5)
After I get the various pieces I want to concatenate them in the required format so I do the following in order to switch from date and int types to character data:
declare @miljul varchar(5)
declare @y int = year(getdate());
declare @yearpart varchar(4);
set @yearpart = convert(varchar(4), @y)
set @miljul = RIGHT(@yearpart,2) + @miljul
return @miljul
I though varchar was supposed to preserve the leading zeros? Any ideas on how to get that to stick?
Thank you!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

This works correctly for me:

declare @miljul varchar(5)

declare @y int

set @y = year('1/1/2008');

declare @yearpart varchar(4);

set @miljul = '312'

set @yearpart = convert(varchar(4), @y)

print @yearpart

set @miljul = RIGHT(@yearpart,2) + @miljul

print @miljul

Does it for you?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The problem is that you're trying to use RETURN.

Return is (typically) for the result code for a proc (0= success, etc) and it is always an integer. Rather use either SELECT to return it as a resultset from (I assume) a stored proc, or use an output parameter of the correct type.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

CREATE FUNCTION dbo.fnGetMilitaryDate

(

@Sample DATETIME

)

RETURNSCHAR(5)

AS

BEGIN

RETURN(

SELECTSUBSTRING(DATENAME(YEAR, @Sample) + DATENAME(DAYOFYEAR, @Sample), 3, 5)

)

END

N 56°04'39.16"
E 12°55'05.25"

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 4

Thanks everyone for responding.

I defined this function as a scalar function and so it required an argument with the return statement.

My issue was I had the return defined as an int originally and didn't change that to varchar(5).

Your input helped me find that so thank you!

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;