Date convert in derived Column

Question

I have a date column in a format like shown below.

Date

02OCT1990

05OCT1992

02JAN2003

15JUN2010

How to convert the column to ddmmyyyy(02101990)?

Thanks in advance!

Answer 1

Hi @Landon S,

You may use the expression in the derived Column.

SUBSTRING(Date,1,2) +   (SUBSTRING(Date,3,3) == "JAN" ? "01" : SUBSTRING(Date,3,3) == "FEB" ? "02" : SUBSTRING(Date,3,3) == "MAR" ? "03" : SUBSTRING(Date,3,3) == "APR" ? "04" :  SUBSTRING(Date,3,3) == "MAY" ? "05" : SUBSTRING(Date,3,3) == "JUN" ? "06" : SUBSTRING(Date,3,3) == "JUL" ? "07" : SUBSTRING(Date,3,3) == "AUG" ? "08" : SUBSTRING(Date,3,3) == "SEP" ? "09" : SUBSTRING(Date,3,3) == "OCT" ? "10" : SUBSTRING(Date,3,3) == "NOV" ? "11" : SUBSTRING(Date,3,3) == "DEC" ? "12" : "") +SUBSTRING(Date,6,4)

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

It would be best if you had the STUFF function to insert a space after the day part of the date to make it a format SQL Server can recognize as a date.

For example:

SELECT
    RIGHT('0' + CONVERT(NVARCHAR, DAY(CAST(STUFF(TheDate, 3, 0, ' ') AS DATE)), 2), 2) +
    RIGHT('0' + CONVERT(NVARCHAR, MONTH(CAST(STUFF(TheDate, 3, 0, ' ') AS DATE)), 2) +
    RIGHT(Date, 4) AS FormattedDate
FROM YourTable;