January 9, 2022 at 12:07 am
How to add zero after 4 numbers in sql? Example - 2019011
Currently i am getting data like 201911. But i want zero after first 4 numbers.
January 9, 2022 at 12:41 am
How to add zero after 4 numbers in sql? Example - 2019011
Currently i am getting data like 201911. But i want zero after first 4 numbers.
Is it really a number or is it a string that you're working with?
And, if you're the one creating the origin 201911, please post the code so we can show you how to do it in a single step.
--Jeff Moden
January 9, 2022 at 1:27 am
column name is Year and its data type is nvarchar(50).
Currenlty data is comming as
201702
201801
202013
Format of data should be like below
2017002
2018001
2020013
January 9, 2022 at 2:43 am
LEFT(SourceString,4) & FORMAT(RIGHT(SourceString,2),"000")
?
January 9, 2022 at 3:15 am
Ok... first of all, I STRONGLY recommend NOT using the (ugh!) severely performance challenged FORMAT function! 😀
After that, the following will do the trick and "forgive" original formatting mistakes like pietlinden was trying to do with his suggestion. Of course, you'll need to change @Year in the SELECT to your actual column name and you won't need the DECLARE. This is demo code.
DECLARE @Year NVARCHAR(50) = '201901';
SELECT LEFT(@Year,4) + RIGHT('000'+RIGHT(@Year,2),3);
Also, you should have a little talk with the "designers" of all this. It's bad enough using NVARCHAR(anything) for a "digits only" column but, even if they can justify that due to some down the road concatenation (which may be another problem), there's no need for something that will only contain 6 characters to be given a length of 50.
--Jeff Moden
January 9, 2022 at 10:03 am
Borrowing Jeff's code a little, here is another version:
DECLARE @Year NVARCHAR(50) = N'201901';
SELECT STUFF(@Year, 5, 0, '0');
Not only is the NVARCHAR() datatype a terrible choice, but calling the column 'year' is also ill-advised, as
a) Year() is a T-SQL function, which is potentially confusing, and
b) Its contents are not just years
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 9, 2022 at 3:23 pm
The only reason I didn't use STUFF is because if you DO use the code on data that has already been modified by the use of STUFF, then it's going to not do it correctly the next time.
For example, 201912 would correctly become 2019012 the first time it was used. If someone screwed up and ran that conversion on that resulting data, you'd end up with 20190012, which would be incorrect. It's not that STUFF is bad. It's just not a bullet proof solution in this particular case. The methods that pietlinden was aiming at and that I used are fairly well bullet proof when it comes to reruns on the same data.
Of course, I really don't understand why this need exists. If it's a year and a month number or even a year and a week number, it's a waste of time. If it's a year and a day number (kind of Julian Date), then it's wrong.
So, how about it, mcfarlandparkway... can you explain a bit more just to satisfy our curiosity on this?
--Jeff Moden
January 9, 2022 at 5:54 pm
My guess is that it is probably some type of Julian date - having the year (first 4 digits) and day of year. If that is the case - then all proposed solutions will not work correctly. Each one would need to check the length of the input string, and if less than 7 then insert a zero or modify the proposed solutions to actually work correctly.
Declare @testData table (InputYear nvarchar(50)); --nvarchar(50)???
Insert Into @testData (InputYear)
Values ('201901'), ('201932'), ('2019131'), ('2019365'), ('2019044');
Select *
, ConvertedValue = iif(len(td.InputYear) < 7, stuff(td.InputYear, 5, 0, '0'), td.InputYear)
, JeffModen = LEFT(td.InputYear,4) + RIGHT('000'+RIGHT(td.InputYear,2),3)
, ConvertedValue2 = concat(left(td.InputYear, 4), right('000' + right(td.InputYear, 3), 3))
From @testData td;
This will be repeatable and will not convert/update if run against a value that had already been converted. As you can see - if the values already have 3 digits following the year - then Jeff Moden's solution breaks, and you can't just change it to look at the last 3 digits because that includes part of the year and returns the wrong value.
Of course, if the input value has a length other than 6 or 7 (which is entirely possible based on the declared data type) then all bets are off.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 10, 2022 at 3:48 am
My guess would be that it cannot be YYYYDDD because the first D is always a 0. Perhaps YYYYoWW but why the bloody zero?
Now, if we could just get mcfarlandparkway to tell us.
--Jeff Moden
January 10, 2022 at 7:22 pm
One of the standard formats displaying a date in the ISO standards is YYYY-DDD, where DDD is a string between "001" and"365" or "366" (depending on leap years). However, I can say with absolute certainty that nvarchar(50) makes no sense for any display format for dates. There is not a lot of call for a date written in Chinese characters. It's longer than many of the epic poems in that language. So why are you supporting it? This is usually the sort of crap DDL that beginners put in their code because they don't do take the time to actually design data.
You should be formatting and checking your data as it comes in in the input tier of your system. If you do need to go to what is called the "ordinal date" format (not the same thing as the Julian date that astronomers use in; this is sometimes called a Julianized date format, but technically, dates can be Julianized over longer periods than one year.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply