CODEWITHSUNDEEP
postgresqldatetrim
Mani Deep
Mani Deep

Reputation: 1356

string to date conversion with condition - postgresql

I have a Table with coloumnname description

   description  //character varying
LZ000834_28-02-14
LZ000834_28-02-14
LA20683_30-04-15
LA20683_30-04-15
LA20300_31-01-15
LA20300_31-01-15
LA20264_31-01-15
LA20264_31-01-15
LAN2078_31-03-15
LAN2078_31-03-15
L2Z82736_31_03_2015 //this is rare case but this kind of dates also possible

here description means batchname_expirydate

my question is how do I separate batchname and expiry date and display only the description whose expiration date is >now+120 days

thanks,

Upvotes: 1

Views: 134

Answers (2)

foibs
foibs

Reputation: 3406

The correct solution is to normalize your data, add a column to your table which contains the expiry date of each product, add an index to that column and select accordingly.

The quick way is to use a regex

with product_dates AS (
    select description, case when val[4]::int > 1900 then 
        (val[4]||'-'||val[3]||'-'||val[2])::date else
        ((val[4]::int+2000)||'-'||val[3]||'-'||val[2])::date end as expir_date
    from ( 
        select description,regexp_matches(description, '([a-zA-Z0-9]+)_([0-9]+)[-_]([0-9]+)[-_]([0-9]+)') as val 
        from your_table_name
    ) a
), expiring_dates AS (
    select description from product_dates where expir_date > now() + '120 days'
)
select description from expiring_dates

I'm sure the above can be optimized a bit, but that's not the point, is it? Don't forget to change your_table_name with your real table name

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

Agreeing with previous comments that this should be normalized, In order to get the dates from those strings you can try this:

select to_date(substring(col1 from position('_' in col1) + 1), 'DD-MM-YY')
from tab1;

This will transform the text after the first _ to a date.

sqlfiddle demo

Upvotes: 1

Related Questions