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

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.

Thanks
Todd P Payne

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...

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 ReportingDB

truncate table dimcalendarrevenue

declare @startdate date
declare @enddate date
declare @firstdayofmonth int
declare @firstdayofweek int
declare @tablename nvarchar(100)
declare @quartername nvarchar(100)

if @startdate < '01/01/1971'
begin
set @startdate = '01/01/1971'
end
else set @startdate = '01/01/2000'

set @enddate = '12/31/2020'
set @firstdayofmonth = 19
set @firstdayofweek = 5 --week day numbers are (1 = monday, 2 = tuesday, 3 = wednesday, 4 = thursday, 5 = friday, 6 = saturday, 7 = sunday)
set datefirst @firstdayofweek
set @tablename = 'dimcalendarrevenue'
set @quartername = 'Q'


while @startdate <= @enddate
begin

insert 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
end
from DimCalendarRevenue drc

update DimCalendarRevenue
set AdjustedWeekOfYear = case
when drc.AdjustedWeekOfYear = 53
then 1
else drc.adjustedweekofyear
end
from DimCalendarRevenue drc


-----------------------------------------------------------
-- Update Holidays --
-----------------------------------------------------------

-- New Year's Day

update DimCalendarRevenue
set HolidayFlag = 1,
HolidayType = 'US Federal Holiday',
HolidayName = 'New Year''s Day'
where CalendarMonth = 1
and CalendarDayOfMonth = 1

-- Independence Day

update DimCalendarRevenue
set HolidayFlag = 1,
HolidayType = 'US Federal Holiday',
HolidayName = 'Independence Day'
where CalendarMonth = 7
and CalendarDayOfMonth = 4

-- Veteran's Day

update DimCalendarRevenue
set HolidayFlag = 1,
HolidayType = 'US Federal Holiday',
HolidayName = 'Veteran''s Day'
where CalendarMonth = 11
and CalendarDayOfMonth = 11

-- Christmas Day

update DimCalendarRevenue
set HolidayFlag = 1,
HolidayType = 'US Federal Holiday',
HolidayName = 'Christmas Day'
where CalendarMonth = 12
and 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 drc
where drc.CalendarMonth = 1
and 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 drc
where drc.CalendarMonth = 2
and 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 drc
where drc.CalendarMonth = 5
and 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 drc
where drc.CalendarMonth = 9
and 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 drc
where drc.CalendarMonth = 10
and 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 drc
where drc.CalendarMonth = 11
and 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)

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.

ALTER Function [dbo].[DBA_fnAddLeadingZeros](@Int INT, @TotalLength INT)
AS
BEGIN
   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

Thanks
Todd