Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Function to Add Leading 0's (Zeros) to an INT Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, February 13, 2009 10:37 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, July 8, 2015 10:31 AM Points: 30, Visits: 118
 Comments posted to this topic are about the item Function to Add Leading 0's (Zeros) to an INT
Post #657114
 Posted Thursday, March 5, 2009 7:46 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, April 20, 2016 10:02 AM Points: 28, Visits: 195
 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
Post #669233
 Posted Friday, March 6, 2009 6:38 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, July 8, 2015 10:31 AM Points: 30, Visits: 118
 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 9, 2009 8:04 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, April 23, 2014 8:52 AM Points: 4, Visits: 48
 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)
Post #694022
 Posted Thursday, April 9, 2009 3:02 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, April 20, 2016 10:02 AM Points: 28, Visits: 195
 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)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`ThanksTodd
Post #694465

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.