RE: Function to Add Leading 0's (Zeros) to an INT

Todd Payne — Thu, 09 Apr 2009 15:02:24 GMT

I just read your response. You are definitely right about encapsulating the logic into a function. It does make things much cleaner, and it never occurred to me that I would need to pad more than 10 leading zeros to an integer. I guess that shows my level of experience.I also noticed that neither of our methods work with negative integers.Here is my solution to that problem. Please forgive the word wrap.[code]ALTER Function [dbo].[DBA_fnAddLeadingZeros](@Int INT, @TotalLength INT)ASBEGIN DECLARE @Return VARCHAR(2000); --IF @TotalLength > 100 SELECT @TotalLength = 100 IF @Int < 0 BEGIN -- moves the - sign to the first charactor SELECT @Return = Replicate('0',@TotalLength) + CAST(ABS(@Int) AS VARCHAR); SELECT @Return = '-' + Right(@Return,@TotalLength -1); END ELSE BEGIN SELECT @Return = Replicate('0',@TotalLength) + CAST(@Int AS VARCHAR); SELECT @Return = Right(@Return,@TotalLength); END RETURN @return;END[/code]ThanksTodd

RE: Function to Add Leading 0's (Zeros) to an INT

sang-lee — Thu, 09 Apr 2009 08:04:39 GMT

Just thought that I would add to the discussion...I am no DBA....but forced to work in this area....so...pardon any bad code. I'm starting to write a stored procedure that will generate a US calendar based on the parameters specified...here it is so far...I have only done a brief validation of it...It's something to look at and if you have a better way to do it, that would be great...use ReportingDBtruncate table dimcalendarrevenuedeclare @startdate datedeclare @enddate datedeclare @firstdayofmonth intdeclare @firstdayofweek intdeclare @tablename nvarchar(100)declare @quartername nvarchar(100)if @startdate < '01/01/1971'begin set @startdate = '01/01/1971'endelse set @startdate = '01/01/2000'set @enddate = '12/31/2020'set @firstdayofmonth = 19set @firstdayofweek = 5 --week day numbers are (1 = monday, 2 = tuesday, 3 = wednesday, 4 = thursday, 5 = friday, 6 = saturday, 7 = sunday)set datefirst @firstdayofweekset @tablename = 'dimcalendarrevenue'set @quartername = 'Q'while @startdate <= @enddatebegininsert into DimCalendarRevenue-------------------------------------------------------------- regular calendar --------------------------------------------------------------select convert(int,(right(replicate('0',4) + convert(nvarchar(4),datepart(yyyy,@startdate)),4) + right(replicate('0',2) + convert(nvarchar(2),datepart(mm,@startdate)),2) + right(replicate('0',2) + convert(nvarchar(2),datepart(dd,@startdate)),2))) as datekey, @startdate as [calendardate], year(@startdate) as [calendaryear], datepart(dy,@startdate) as [calendardayofyear], month(@startdate) as [calendarmonth], day(@startdate) as [calendardayofmonth], datepart(wk,@startdate) as [calendarweekofyear], datename(weekday,@startdate) as [calendarweekdayname], datepart(dw,@startdate) as [calendarweekdaynumber], datename(mm,@startdate) as [calendarmonthname], case when month(@startdate) in (1,2,3) then '1' when month(@startdate) in (4,5,6) then '2' when month(@startdate) in (7,8,9) then '3' when month(@startdate) in (10,11,12) then '4' end as [calendarquarter], @quartername + CONVERT(nvarchar(1), case when month(@startdate) in (1,2,3) then '1' when month(@startdate) in (4,5,6) then '2' when month(@startdate) in (7,8,9) then '3' when month(@startdate) in (10,11,12) then '4' end) as [calendarquartername], ----------------------------------------------------------- -- adjusted calendar -- ----------------------------------------------------------- case when datepart(mm,@startdate) = 1 and @firstdayofmonth <> 1 then case when datepart(dd,@startdate) < @firstdayofmonth then year(dateadd(yy,-1,@startdate)) else year(@startdate) end else year(@startdate) end as [adjustedyear], datepart(dy,dateadd(dd,-@firstdayofmonth + 1,@startdate)) as [adjusteddayofyear], case when @firstdayofmonth <> 1 then case when datepart(dd,@startdate) < @firstdayofmonth then month(dateadd(mm,-1,@startdate)) else month(@startdate) end else month(@startdate) end as [adjustedmonth], case when (day(@startdate) < @firstdayofmonth) and @firstdayofmonth <> 1 then datediff(dd,dateadd(mm,-1,dateadd(dd,@firstdayofmonth-day(@startdate),@startdate)),@startdate) + 1 when (day(@startdate) > @firstdayofmonth) and @firstdayofmonth <> 1 then day(@startdate) - @firstdayofmonth + 1 when day(@startdate) = @firstdayofmonth then 1 else day(@startdate) end as [adjusteddayofmonth], case when datepart(wk,@startdate) = 53 then 1 else datepart(wk,@startdate) end as [adjusted52weeks], ceiling(cast(datepart(dy,dateadd(dd,-@firstdayofmonth + 1,@startdate)) as float)/7) as [adjustedweekofyear], datename(weekday,@startdate) as [adjustedweekdayname], datename(mm,dateadd(dd,-@firstdayofmonth+1,@startdate)) as [adjustedmonthname], case when (case when @firstdayofmonth <> 1 then case when datepart(dd,@startdate) < @firstdayofmonth then month(dateadd(mm,-1,@startdate)) else month(@startdate) end else month(@startdate) end) in (1,2,3) then 1 when (case when @firstdayofmonth <> 1 then case when datepart(dd,@startdate) < @firstdayofmonth then month(dateadd(mm,-1,@startdate)) else month(@startdate) end else month(@startdate) end) in (4,5,6) then 2 when (case when @firstdayofmonth <> 1 then case when datepart(dd,@startdate) < @firstdayofmonth then month(dateadd(mm,-1,@startdate)) else month(@startdate) end else month(@startdate) end) in (7,8,9) then 3 when (case when @firstdayofmonth <> 1 then case when datepart(dd,@startdate) < @firstdayofmonth then month(dateadd(mm,-1,@startdate)) else month(@startdate) end else month(@startdate) end) in (10,11,12) then 4 end as [adjustedquarter], @quartername + CONVERT(nvarchar(1),case when (case when @firstdayofmonth <> 1 then case when datepart(dd,@startdate) < @firstdayofmonth then month(dateadd(mm,-1,@startdate)) else month(@startdate) end else month(@startdate) end) in (1,2,3) then 1 when (case when @firstdayofmonth <> 1 then case when datepart(dd,@startdate) < @firstdayofmonth then month(dateadd(mm,-1,@startdate)) else month(@startdate) end else month(@startdate) end) in (4,5,6) then 2 when (case when @firstdayofmonth <> 1 then case when datepart(dd,@startdate) < @firstdayofmonth then month(dateadd(mm,-1,@startdate)) else month(@startdate) end else month(@startdate) end) in (7,8,9) then 3 when (case when @firstdayofmonth <> 1 then case when datepart(dd,@startdate) < @firstdayofmonth then month(dateadd(mm,-1,@startdate)) else month(@startdate) end else month(@startdate) end) in (10,11,12) then 4 end) as [adjustedquartername], ----------------------------------------------------------- -- date facts -- ----------------------------------------------------------- case when datename(weekday,@startdate) = 'sunday' or datename(weekday,@startdate) = 'saturday' then 'weekend' else 'weekday' end as [weekdaytype], 0 as [holidayflag], '' as [holidaytype], '' as [holidayname] set @startdate = dateadd(dd,1,@startdate)end ----------------------------------------------------------- -- Update Adjusted Weeks For Reporting -- ----------------------------------------------------------- update DimCalendarRevenue set AdjustedWeekOfYear = case when DateKey >= (select drc2.DateKey from DimCalendarRevenue drc2 where drc2.AdjustedWeekOfYear = drc.AdjustedWeekOfYear and drc2.AdjustedYear = drc.AdjustedYear and drc2.calendarweekdaynumber = 1) then AdjustedWeekOfYear + 1 else adjustedweekofyear endfrom DimCalendarRevenue drcupdate DimCalendarRevenue set AdjustedWeekOfYear = case when drc.AdjustedWeekOfYear = 53 then 1 else drc.adjustedweekofyear endfrom DimCalendarRevenue drc ----------------------------------------------------------- -- Update Holidays -- ----------------------------------------------------------- -- New Year's Day update DimCalendarRevenue set HolidayFlag = 1, HolidayType = 'US Federal Holiday', HolidayName = 'New Year''s Day'where CalendarMonth = 1and CalendarDayOfMonth = 1-- Independence Day update DimCalendarRevenue set HolidayFlag = 1, HolidayType = 'US Federal Holiday', HolidayName = 'Independence Day'where CalendarMonth = 7and CalendarDayOfMonth = 4-- Veteran's Day update DimCalendarRevenue set HolidayFlag = 1, HolidayType = 'US Federal Holiday', HolidayName = 'Veteran''s Day'where CalendarMonth = 11and CalendarDayOfMonth = 11-- Christmas Day update DimCalendarRevenue set HolidayFlag = 1, HolidayType = 'US Federal Holiday', HolidayName = 'Christmas Day'where CalendarMonth = 12and CalendarDayOfMonth = 25-- Birthday of Martin Luther King, Jr. update DimCalendarRevenue set HolidayFlag = 1, HolidayType = 'US Federal Holiday', HolidayName = 'Birthday of Martin Luther King, Jr. (Martin Luther King Day)'from DimCalendarRevenue drcwhere drc.CalendarMonth = 1and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth from (select top 3 drc2.calendardayofmonth from DimCalendarRevenue drc2 where drc2.CalendarYear = drc.CalendarYear and drc2.CalendarMonth = 1 and drc2.CalendarWeekDayName = 'Monday' order by drc2.CalendarDayOfMonth asc) td order by td.calendardayofmonth desc)-- Washington's Birthday update DimCalendarRevenue set HolidayFlag = 1, HolidayType = 'US Federal Holiday', HolidayName = 'Washington''s Birthday (Presidents'' Day)'from DimCalendarRevenue drcwhere drc.CalendarMonth = 2and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth from (select top 3 drc2.calendardayofmonth from DimCalendarRevenue drc2 where drc2.CalendarYear = drc.CalendarYear and drc2.CalendarMonth = 2 and drc2.CalendarWeekDayName = 'Monday' order by drc2.CalendarDayOfMonth asc) td order by td.calendardayofmonth desc) -- Memorial Day update DimCalendarRevenue set HolidayFlag = 1, HolidayType = 'US Federal Holiday', HolidayName = 'Memorial Day'from DimCalendarRevenue drcwhere drc.CalendarMonth = 5and drc.CalendarDayOfMonth = (select top 1 drc2.calendardayofmonth from DimCalendarRevenue drc2 where drc2.CalendarYear = drc.CalendarYear and drc2.CalendarMonth = 5 and drc2.CalendarWeekDayName = 'Monday' order by drc2.CalendarDayOfMonth desc) -- Labor Day update DimCalendarRevenue set HolidayFlag = 1, HolidayType = 'US Federal Holiday', HolidayName = 'Labor Day'from DimCalendarRevenue drcwhere drc.CalendarMonth = 9and drc.CalendarDayOfMonth = (select top 1 drc2.calendardayofmonth from DimCalendarRevenue drc2 where drc2.CalendarYear = drc.CalendarYear and drc2.CalendarMonth = 9 and drc2.CalendarWeekDayName = 'Monday' order by drc2.CalendarDayOfMonth asc)-- Columbus Day update DimCalendarRevenue set HolidayFlag = 1, HolidayType = 'US Federal Holiday', HolidayName = 'Columbus Day'from DimCalendarRevenue drcwhere drc.CalendarMonth = 10and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth from (select top 2 drc2.calendardayofmonth from DimCalendarRevenue drc2 where drc2.CalendarYear = drc.CalendarYear and drc2.CalendarMonth = 10 and drc2.CalendarWeekDayName = 'Monday' order by drc2.CalendarDayOfMonth asc) td order by td.calendardayofmonth desc) -- Thanksgiving Day update DimCalendarRevenue set HolidayFlag = 1, HolidayType = 'US Federal Holiday', HolidayName = 'Thanksgiving Day'from DimCalendarRevenue drcwhere drc.CalendarMonth = 11and drc.CalendarDayOfMonth = (select top 1 td.calendardayofmonth from (select top 4 drc2.calendardayofmonth from DimCalendarRevenue drc2 where drc2.CalendarYear = drc.CalendarYear and drc2.CalendarMonth = 11 and drc2.CalendarWeekDayName = 'Thursday' order by drc2.CalendarDayOfMonth asc) td order by td.calendardayofmonth desc)

RE: Function to Add Leading 0's (Zeros) to an INT

jwalker8680 — Fri, 06 Mar 2009 06:38:53 GMT

I could have taken the function down to a single line as well:RETURN RIGHT(replicate('0',@TotalLength) + CAST(@Int AS VARCHAR),@TotalLength)or.. SELECT RIGHT(replicate('0',6) + '1234',6)...And I could use the code above instead of calling the function... but I think the way of using the function is much cleaner and keeps the code that uses it much cleaner.In your example... if you need it longer (25 in this example)... SELECT RIGHT(power(10,25) + 123456789,25)... <-- This will fail... Mine won't.Additionally, not having this in a function would make the example on my post extreamly long... For one off's... Yeah... you could code it right there, but if you need several items in a select converted to having leading 0's it would be tedious, messy and long... There is more than one way to skin this cat, but this is my preference...

RE: Function to Add Leading 0's (Zeros) to an INT

Todd Payne — Thu, 05 Mar 2009 07:46:46 GMT

I use the power function to accomplish this task.SELECT RIGHT(power(10,@TotalLength) + @myInt,@TotalLength)I did use SELECT RIGHT(Convert(varchar,(power(10,@TotalLength) + @myInt)),@TotalLength)but found that I could eliminate the convert function from the statement.ThanksTodd P Payne

Function to Add Leading 0's (Zeros) to an INT

jwalker8680 — Fri, 13 Feb 2009 22:37:52 GMT

Comments posted to this topic are about the item [B]Function to Add Leading 0's (Zeros) to an INT[/B]

SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Joshua A. Walker / Function to Add Leading 0's (Zeros) to an INT / Latest Posts

RE: Function to Add Leading 0's (Zeros) to an INT

RE: Function to Add Leading 0's (Zeros) to an INT

RE: Function to Add Leading 0's (Zeros) to an INT

RE: Function to Add Leading 0's (Zeros) to an INT

Function to Add Leading 0's (Zeros) to an INT