Creating a Date Field from a big string Field

Question

Good Morning,

I have a Table and the field(type:string) "properties_content" is always filled as the following pattern:

"Month DD, YYYY"/"Month D, YYYY" + "a Written Review"

Like:

May 18, 2023 Loved the message
January 1, 2022 Nice one
February 13, 2023 Thanks

I'm trying to address this using the following code, but had no success:

SELECT id, properties_content,
case
    WHEN SUBSTRING(properties_content, 1, 3) = 'Jan' THEN
        CASE 
            CAST (WHEN CHARINDEX(',',properties_content) = 11 THEN SUBSTRING(properties_content, 1, 16)
            ELSE SUBSTRING(properties_content, 1, 15)
        END) AS DATE
    END AS date_column
FROM table

where am i going wrong?

(In the exemple above i'm adressing just January, but the idea is to follow the same logic for other months)

Note that tables have columns, not fields. (But a date has a year field, a month field and a day field...) — jarlh, Apr 17 '23 at 20:35

Kostya · Accepted Answer · 2023-04-18T20:59:58.150

1

this should work.

SELECT id, properties_content,  
     convert(date , LEFT(properties_content,  charindex(',', properties_content) +5) ) AS date_column
FROM table

edited Apr 18 '23 at 20:59

answered Apr 17 '23 at 21:01

Kostya

1,567
1
9
15

Hey Kostya , thanks - It almost did, the Left and Charindex function (without using CAST) were both able to adress separating the date from the rest of the Written review, but in some cases (as we can see in this image), part of the review still came - https://imgur.com/a/JhchqIx | When i use the cast function however this does not seem to work , the values came all null (MONGODB) https://imgur.com/a/mXTnmTy – marceloasr Apr 18 '23 at 13:02
Tried to do: `SELECT id, CAST(LEFT(properties_content, charindex(' ',properties_content, charindex(',', properties_content) +2)-1) AS DATE) AS date_column, LEFT(properties_content, charindex(' ',properties_content, charindex(',', properties_content) +2)-1) AS string FROM Table` the output is here https://imgur.com/a/IaQbuAJ - as we can see the CAST did not work (don't know why, is there a CONVET() solution?) and for some dates i still carrying part of the stars/review to the "string" column – marceloasr Apr 18 '23 at 14:28
1

the query should remove stars now, but not sure why cast is not working. – Kostya Apr 18 '23 at 21:00

Creating a Date Field from a big string Field

1 Answers1