Extract date from String column in sql

Question

I have a column as current_data and it has data which is of type string and data as {"settlement_date":"2018-07-21"}. My question is that for each trade the settlement date will be diffrent and i want to extract the date i.e 2018-07-21 from the column current_data for each trade. I tried using select to_char(date_trunc(d.current_data,'YYYY-MM-DD')) as "Current_date" also i have tried the trim fuction but it does not work

Which DBMS are you using? This seems like JSON format to me. Are you sure internally it's stored as string (text)? — Kamil Gosciminski
– Kamil Gosciminski, Commented Jun 1, 2018 at 20:34

Kamil Gosciminski · Accepted Answer · 2018-06-01 20:37:45Z

1

It looks like JSON data. Since you're saying it's a text column internally you could use substring function to cut only the data you're looking for.

select substring(current_data from 21 for 10) from yourtable

You start taking the substring from 21 character and specify that it's length will be the next 10 characters.

With your sample data the result would be

db=# select substring('{"settlement_date":"2018-07-21"}' from 21 for 10);
 substring
------------
 2018-07-21

Beware though that this solution relies on length of the string and is designed for static input where the extracted substring is always within the same position.

answered Jun 1, 2018 at 20:37

Kamil Gosciminski

17.4k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TAJINDER SINGH Over a year ago

I have tried that got error @Kamil G. Function date_trunc(unknown, varchar) does not exist, or permission is denied for date_trunc(unknown, varchar)

Kamil Gosciminski Over a year ago

There is no date_trunc function in my answer. Are you sure you've tried my solution and not yours?

Kamil Gosciminski Over a year ago

@TAJINDERSINGH this is unclear to me. Update your answer with all relevant information so that people could provide help.

TAJINDER SINGH Over a year ago

when I ran query : select d.current_data , (select substring('current_data' from 21 for 10)) as modified_data from DAILY_AMENDED_TRADE_REPORT d INNER JOIN TRADE_REPORT t ON d.trade_id=t.trade_id and d.source_system=t.source_system where business_date='2018-06-01' Answer i got is current_data:{"settlement_date":"2018-06-20"} and modified_data is empty modified_data

Kamil Gosciminski Over a year ago

@TAJINDERSINGH you need to remove apostrophes around current_data. It's a column reference, so it shouldn't be a string. Rephrase your query at start to: select d.current_data, substring(d.current_data from 21 for 10) as modified_data from ... You also don't need another select in there (I removed it)

Collectives™ on Stack Overflow

Extract date from String column in sql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related