Reformatting Text from "yyyymmdd" or "yyyyddmm" format to "dd MMM yyyy&quot-CodePudding

In SQL Server, I want to convert text from yyyymmdd or yyyyddmm format to dd MMM yyyy.

Example: From 20211231 to 31 Dec 2021 and From 20213112 to 31 Dec 2021

NB: In one column my data is in yyyymmdd format. I also want to learn: if I store data in yyyyddmm format instead of yyyymmdd, then how can I be able to convert it into dd MMM yyyy format?

CodePudding user response：

The solution is fix your design; no arguments, it's fact. It seems like you have 2 columns here, not one, so hopefully the formatting is the consistent in the same column. I'm going to call these columns yyyyMMdd and yyyyddMM respectively here, to demonstrate the solutions for both, though obviously your columns wouldn't have such (silly) names:

--Set the yyyyddMM date to an ISO format
UPDATE dbo.YourTable
SET yyyyddMM = LEFT(yyyyddMM, 4)   RIGHT(yyyyddMM, 2)   SUBSTRING(yyyyddMM, 5, 2);

--Get rid of crap dates
UPDATE dbo.YourTable
SET yyyyddMM = NULL
WHERE TRY_CONVERT(date, yyyyddMM) IS NULL;

UPDATE dbo.YourTable
SET yyyyMMdd = NULL
WHERE TRY_CONVERT(date, yyyyMMdd) IS NULL;
GO
--Fix the data types
ALTER TABLE dbo.YourTable ALTER COLUMN yyyyMMdd date;
ALTER TABLE dbo.YourTable ALTER COLUMN yyyyddMM date;

Then you control the format in your presentation layer, what ever that might be, not the SQL.