sqlmongodbcastingsql-convert

Creating a Date Field from a big string Field

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)

Solution

  • this should work.

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