Leading zeroes substring query in SQL Server

Question

Leading zeroes substring query in SQL Server

SQListic

SSCertifiable

Points: 6904
More actions
March 16, 2015 at 4:12 pm

#298684

Can Someone please help me in padding leading zeroes for the months in End date
Example: actual data is like 6222007
,11301998 in end date column the following query works fine for 11301998 and converts it as 19981130 which was correct.
But 6222007
fails because month has no leading zero and it converts it as 0076222 which is wrong.
How can i make it as 20070622 with the following code
select (case when replace (ltrim(rtrim(ltrim([end date]))), '|', '') in ('99999999','00000000') then NULL
else substring ([END DATE],5,4)+SUBSTRING([END DATE],1,2)+SUBSTRING([END DATE],3,2) end) as ConvEnd_date
from Mydatabase.dbo.[AccountTable]
Thanks
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

SQListic (3/16/2015)
Can Someone please help me in padding leading zeroes for the months in End date
Example: actual data is like 6222007
,11301998 in end date column the following query works fine for 11301998 and converts it as 19981130 which was correct.
But 6222007
fails because month has no leading zero and it converts it as 0076222 which is wrong.
How can i make it as 20070622 with the following code
select (case when replace (ltrim(rtrim(ltrim([end date]))), '|', '') in ('99999999','00000000') then NULL
else substring ([END DATE],5,4)+SUBSTRING([END DATE],1,2)+SUBSTRING([END DATE],3,2) end) as ConvEnd_date
from Mydatabase.dbo.[AccountTable]
Thanks

select (case when replace (ltrim(rtrim(ltrim(x.[New End Date]))), '|', '') in ('99999999','00000000') then NULL

else substring ([New End Date],5,4)+SUBSTRING([New End Date],1,2)+SUBSTRING([New End Date],3,2) end) as ConvEnd_date

from Mydatabase.dbo.[AccountTable]

cross apply (

select right('0'+RTRIM([END DATE]),8) as [New End Date]

) x

Then look at storing dates as dates, so you don't have this mess - if you can make that change.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SQListic SSCertifiable Points: 6904 More actions · Answer 2

Thank u so much

I also did it with RIGHT FUNCTION

SELECT substring(RIGHT('00000000' + CAST([END DATE] AS VARCHAR), 8),5,4)+substring(RIGHT('00000000' + CAST([END DATE] AS VARCHAR), 8),1,2)+

substring(RIGHT('00000000' + CAST([END DATE] AS VARCHAR), 8),3,2) from

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

SQListic (3/16/2015)
Thank u so much
I also did it with RIGHT FUNCTION
SELECT substring(RIGHT('00000000' + CAST([END DATE] AS VARCHAR), 8),5,4)+substring(RIGHT('00000000' + CAST([END DATE] AS VARCHAR), 8),1,2)+
substring(RIGHT('00000000' + CAST([END DATE] AS VARCHAR), 8),3,2) from

Do you ever have any dates that show up as only 6 digits (mdyyyy) or just 4 digits (mdyy)? Also, what is the datatype of the [End Date] column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)