CODEWITHSUNDEEP
sqlamazon-redshift
user3369545
user3369545

Reputation: 411

Invalid value of MM in source string

I am trying to extract the date out of the orderstatus field in the below table

orderstatus orddate Soldorstockdate
*SLD 05/11/2022 5/1/2022 5/11/2022
37141 SLD BAM 5/5/2022 5/5/2022
*SLD 5/14/22 Heman 5/3/2022 5/14/2022
37938 STOCK 5/18/22 PER SARA 5/18/2022 5/18/2022
*SLD BOBBY LANE IDS#2347509 5/16/2022 5/16/2022
*SLD F.CHAPMAN/S.GUNN 5/31/22 5/10/2022 5/10/2022
*SLD S.STONE STK# 39391 40183 5/5/2022 5/5/2022

If the pattern *SLD appears at the beginning in the orderstatus column and there is a date of the format mm/dd/yy or mm/dd/yyyy then that date needs to be extracted as the soldorstockdate else the orddate should be extracted as the soldorstockdate

I am using the query below

CASE WHEN REGEXP_COUNT("ORDERSTATUS",'^\\*SLD') <> 0
         THEN TO_DATE(REGEXP_SUBSTR("ORDERSTATUS", '[0-9]{1,2}/[0-9]{1,2}') || '/2022','MM/DD/YYYY')
         ELSE DATE("ORDDATE")
END AS ORDERDATE

The above queryis working fine except for the values *SLD BOBBY LANE IDS#2347509 , *SLD F.CHAPMAN/S.GUNN 5/31/22 and *SLD S.STONE STK# 39391 40183 due to which I am getting an error "Invalid value of MM in source string"...

If the orderstatus column has *SLD at the beginning and does not have date of the format mm/dd/yy or mm/dd/yyyy or/mm/dd then orddate should be extracted as the soldorstockdate for that record..

Any inputs will be appreciated

Also the above query is not picking up *SLD BAM 6/9/22

Upvotes: 0

Views: 1208

Answers (1)

Pirate X
Pirate X

Reputation: 3095

You need to handle MM separately since TO_DATE requires specific date format.

SELECT *
     , REGEXP_SUBSTR("ORDERSTATUS", '[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}') AS extracted_date
     , CASE
           WHEN REGEXP_COUNT("ORDERSTATUS", '^\\*SLD') <> 0 THEN
               CASE
                   WHEN LEN(SPLIT_PART(extracted_date, '/', 3)) = 2 THEN TO_DATE(extracted_date, 'MM/DD/YY')
                   WHEN LEN(SPLIT_PART(extracted_date, '/', 3)) = 4 THEN TO_DATE(extracted_date, 'MM/DD/YYYY')
                   END
    END                                                                 AS ORDERDATE

Test Out with rows you provided

WITH ord AS (SELECT '*SLD 05/11/2022' AS orderstatus
             UNION
             SELECT '37141 SLD BAM'
             UNION
             SELECT '*SLD 5/14/22 Heman'
             UNION
             SELECT '37938 STOCK 5/18/22 PER SARA'
             UNION
             SELECT '*SLD BOBBY LANE IDS#2347509'
             UNION
             SELECT '*SLD F.CHAPMAN/S.GUNN 5/31/22'
             UNION
             SELECT '*SLD S.STONE STK# 39391 40183')
SELECT *
     , REGEXP_SUBSTR("ORDERSTATUS", '[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}') AS extracted_date
     , CASE
           WHEN REGEXP_COUNT("ORDERSTATUS", '^\\*SLD') <> 0 THEN
               CASE
                   WHEN LEN(SPLIT_PART(extracted_date, '/', 3)) = 2 THEN TO_DATE(extracted_date, 'MM/DD/YY')
                   WHEN LEN(SPLIT_PART(extracted_date, '/', 3)) = 4 THEN TO_DATE(extracted_date, 'MM/DD/YYYY')
                   END
    END                                                                 AS ORDERDATE
FROM ord

Output

orderstatus extracted_date orderdate
*SLD 05/11/2022 05/11/2022 2022-05-11
*SLD 5/14/22 Heman 5/14/22 2022-05-14
*SLD BOBBY LANE IDS#2347509 NULL
*SLD F.CHAPMAN/S.GUNN 5/31/22 5/31/22 2022-05-31
*SLD S.STONE STK# 39391 40183 NULL
37141 SLD BAM NULL
37938 STOCK 5/18/22 PER SARA 5/18/22 NULL

Upvotes: 1

Related Questions