Date validation in Netezza

Posted: March 19, 2015 in Netezza

In Netezza how to validate the source date. Lets say i have to check if the source date is coming in the formart ‘YYYY-MM-DD’ or not.
To do this lets use reg expression. Run the below query and see the result.

WITH vamshi
AS (SELECT AS my_date
UNION
SELECT ‘2015-02-09’ AS my_date
UNION
SELECT ‘20015-02-09’ AS my_date
UNION
SELECT ‘2015-42-09’ AS my_date
UNION
SELECT ‘2015-02-14’ AS my_date
UNION
SELECT ’22-02-2014′ AS my_date
UNION
SELECT ’02-22-2014′ AS my_date)
SELECT my_date,
Regexp_like(my_date,
‘(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])’)
FROM vamshi

Please add the Regexp_like condition in where clause. By doing so it will filter out the records that not meet the formart.

Ex:- SELECT my_date
FROM vamshi
WHERE Regexp_like(my_date,
‘(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])’)

Below is the code for other common format.

To match a date in mm/dd/yyyy format, use the below
^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d$ .

For dd-mm-yyyy format use
^(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)\d\d$ .

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s