I have a date in a string format I am trying to split this column into (day month year)

so I tried to work with charindex and substring but I always end up having a problem with the month

here is the script I used to split DD MM YYYY

select substring(substring([TRANSACTION_EFFECTIVE_DATE], 
  charindex(' ',[DATE]) 1,charindex(' ',[DATE])
  -(charindex([TRANSACTION_EFFECTIVE_DATE], 1, 1)-10)),1 , 2) AS DD
FROM Date

CodePudding user response：

If the spaces are replaced by dots then the PARSENAME function can be used to get the 4 parts.

SELECT TRANSACTION_EFFECTIVE_DATE
, PARSENAME(REPLACE(TRANSACTION_EFFECTIVE_DATE,' ','.'), 1) AS TR_Year
, PARSENAME(REPLACE(TRANSACTION_EFFECTIVE_DATE,' ','.'), 2) AS TR_Month
, PARSENAME(REPLACE(TRANSACTION_EFFECTIVE_DATE,' ','.'), 3) AS TR_Day
, PARSENAME(REPLACE(TRANSACTION_EFFECTIVE_DATE,' ','.'), 4) AS TR_Weekday
FROM YourTable;

TRANSACTION_EFFECTIVE_DATE	TR_Year	TR_Month	TR_Day	TR_Weekday
vendredi 23 juillet 2021	2021	juillet	23	vendredi
mardi 30 novembre 2021	2021	novembre	30	mardi
mercredi 10 février 2021	2021	février	10	mercredi
lundi 23 août 2021	2021	août	23	lundi
mardi 13 avril 2021	2021	avril	13	mardi
samedi 3 juillet 2021	2021	juillet	3	samedi
jeudi 3 février 2022	2022	février	3	jeudi
jeudi 13 mai 2022	2022	mai	13	jeudi

Test on db<>fiddle here

Side-note. Please consider storing that data as a DATE datatype.

CodePudding user response：

I think you're making the string manipulation a whole lot more complicated than it has to be - as long as you're using French as your language, you can parse the date as is if you just remove the weekday name. That doesn't mean you should have been storing this information as regional and non-standard strings in the first place - why aren't you storing dates using a date data type?

SET LANGUAGE Français;

SELECT DateColumn, HowItShouldHaveBeenStored = TRY_CONVERT(date, 
  SUBSTRING(DateColumn, CHARINDEX(' ', DateColumn), 64), 
  106)
FROM dbo.DateTable;

Output:

• Example db<>fiddle

If you really need the individual date parts:

SET LANGUAGE Français;

;WITH src AS
(
  SELECT DateColumn, d = TRY_CONVERT(date, 
    SUBSTRING(DateColumn, CHARINDEX(' ', DateColumn)   1, 64), 
    106)
  FROM dbo.DateTable
)
SELECT DateColumn, HowItShouldBe = d, 
  DD   = DAY(d),
  MM   = MONTH(d),
  MMM  = LEFT(DATENAME(MONTH, d), 3),
  YYYY = YEAR(d)
FROM src;

Output:

• Example db<>fiddle