Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Function to Add Leading 0's (Zeros) to an INT Expand / Collapse
Author
Message
Posted Friday, February 13, 2009 10:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
Comments posted to this topic are about the item Function to Add Leading 0's (Zeros) to an INT
Post #657114
Posted Thursday, March 05, 2009 7:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 14, 2013 11:27 AM
Points: 27, Visits: 180
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
Post #669233
Posted Friday, March 06, 2009 6:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
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...
Post #670156
Posted Thursday, April 09, 2009 8:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 10:00 AM
Points: 4, Visits: 47
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)

Post #694022
Posted Thursday, April 09, 2009 3:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 14, 2013 11:27 AM
Points: 27, Visits: 180
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
Post #694465
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse