RE: [EXT] Re: How can I detect an invalid date string in SQL?

John,

That works and is much simpler to type. I forgot about the character set grouping not being necessary.

select storagedt, CASE WHEN
REGEXP_LIKE(trim(storagedt),'\d{4}-\d{2}-\d{2}') then dec(date(storagedt)) else 0 end as "as Decimal" from mytable

Michael Salsman

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of John Yeung
Sent: Wednesday, April 17, 2019 09:29
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXT] Re: How can I detect an invalid date string in SQL?

On Wed, Apr 17, 2019 at 11:10 AM Salsman, Michael <Michael.Salsman@xxxxxxxxxxxxxx> wrote:

This worked for me. Not the most flexible but it works and can be altered in the future for you.

select storagedt, CASE WHEN
REGEXP_LIKE(trim(TEST),'[\d][\d][\d][\d][-][\d][\d][-][\d][\d]') then
dec(date(storagedt)) else 0 end as "as Decimal" from mytable

You don't need to put every character being matched in a set. So you could do '\d\d\d\d-\d\d-\d\d' instead.

Moreover, the regular expressions in Db2 for i (not sure about other
databases) support counting, so you can actually do '\d{4}-\d{2}-\d{2}'. This is quite standard among regular expression processors.

John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=jxkTHpqO1H1YZi_-IMcrcLerhvihD9QLMW2-h3jhFwg&s=aMjsrOb2llyQIVrgpx7SgpHUptwI25r1HyGa49ocQEo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=jxkTHpqO1H1YZi_-IMcrcLerhvihD9QLMW2-h3jhFwg&s=kJ_gBeVNCx95S1RApZ-fCHfZm6b-xRQjx_L4YQSWaJc&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=jxkTHpqO1H1YZi_-IMcrcLerhvihD9QLMW2-h3jhFwg&s=ox4f4RUqsq-VFI1Z5esxo9wTVatmIoeK1yA1GkuKeBE&e=