I've got some funny looking dates that I could use some assistance in fixing. I have an example date of 7312018 INT that I need a leading 0 added to but then ALSO flip it around to show it as 20180731 INT.
The length of the bad dates is 7 but should be 8 with the leading 0 so what I was doing for now is
SELECT RIGHT('0' + CAST(s.TestColumn AS VARCHAR(8)), 8) AS TestColumn,
s.OriginalColumn
FROM [database].[schema].[table] s
This gives me 7312018 as 07312018 but am struggling to then have this column show as 20180731. I've confirmed that dates such as 11252014 still show as expected using the query above. Any assistance would be much appreciated!
One thing to remember is that integers are binary values, and so don't have any concept of leading zeros. You could convert to a string and pad the 0 if you want, but as soon as you make it a real integer again that 0 is gone. Anyway, in a database this is the kind of thing you're only supposed to worry about at output time (or even better: after output time, and make it the client code/reporting tool's problem).
But if it were me, I'd parse these into real DATE values. Then I'd stop, and make DATE the new column data type. After all, if I'm going to fix the column, why not actually FIX the column? And since this looks most like SQL Server, it would probably involve the DATEFROMPARTS() function.
But if I absolutely had to keep the column as an integer (and again: this is nearly my last choice; the only thing I'd want less is making it a varchar), I still wouldn't even think about string representations of the values to do the conversion, and instead do it more like this:
(TestColumn % 10000) * 10000 + (TestColumn / 10000)
This is less code, it should be waaaay faster, and it works for both the 7 and 8 length inputs.