I have columns with ddmmyyyy in char format.

I want to change that records into yyyymmdd expected like these

I tried many queries like

convert(varchar,DATE, 112) as DATE

but the result is still ddmmyyyy format and tried also like this

convert(varchar, cast(DATE as date), 112) as DATE

But the result is

Conversion failed when converting date and/or time from character string

any suggestions?

CodePudding user response：

The key problem is that you are storing your date value as a string. You should never do that because it will almost always result in problems further down the line.

Therefore to change the formatting you first have to convert your current string into a valid date and then you use convert to format it as you desire.

SELECT [Date]
    -- First convert to a valid date time - the current format needs to be modified
    , CONVERT(DATE, SUBSTRING([DATE], 1, 2)   '-'   SUBSTRING([DATE], 3, 2)   '-'   SUBSTRING([DATE], 5, 4), 105) [Proper Date Value]
    -- Then convert back to a string in the desired format
    , CONVERT(VARCHAR(8), CONVERT(DATETIME, SUBSTRING([DATE], 1, 2)   '-'   SUBSTRING([DATE], 3, 2)   '-'   SUBSTRING([DATE], 5, 4), 105), 112) [Formatted Date Value]
    -- In fact you can actually just use direct string manipulation in this case
    , SUBSTRING([DATE], 5, 4)   SUBSTRING([DATE], 3, 2)   SUBSTRING([DATE], 1, 2)
FROM (
    VALUES
    ('10112021'),
    ('11112021'),
    ('12112021')
) AS D ([Date]);

Returns:

Note: You should never use varchar without a length as in many circumstances it defaults to a length of 1 which is then the cause of many hard to find issues.