-1

Vertica DB has a column called created_ts, which has records like 3/15/2016, 03/15/2016, 2016/03/15 and 15/03/2016. And I want to filter the records which does not follow the DD/MM/yyyy format.

Ilya
  • 4,583
  • 4
  • 26
  • 51
Midhun
  • 331
  • 2
  • 5
  • 15
  • Could you show us your current query? – Robert Columbia Aug 23 '16 at 05:39
  • I have the query for Teradata DB. which has SYS_CALENDAR.CALENDAR table so that i can join that table and got the output. Below is my query sel count(*)-(SELECT COUNT(*) FROM DP_VEDW_NGN.STG_SITE_CATALYST A LEFT OUTER JOIN SYS_CALENDAR.CALENDAR on (CALENDAR_DATE (FORMAT 'YYYY-MM-DD') (CHAR(10))) = A.date_time AND A.date_time IS NOT NULL) as Invalid_Date,'date_time' AS COLUMN_NAME from DP_VEDW_NGN.STG_SITE_CATALYST – Midhun Aug 23 '16 at 05:47

1 Answers1

0

This should identify most strings that are not valid DD/MM/YYYY dates, though you'd have to add a little more complexity if you want to weed out such invalid dates as April 31 (or Feb 29 on non-leap-years).

select * from my_table
where not (translate(created_ts,'0123456789','9999999999')='99/99/9999' 
           and split_part(created_ts,'/',1) between '01' and '31' 
           and split_part(created_ts,'/',2) between '01' and '12')
KevinKirkpatrick
  • 1,436
  • 1
  • 10
  • 15
  • The query not allowing me to translate because created_ts has timestamp in it.The 'split_part(created_ts,'/',2) between '01' and '12')' which is not return any value . but the same line working for 'split_part(created_ts,'/',2) between '01' and '31')'. unfortunately i do not have any invalid month records. – Midhun Aug 23 '16 at 13:30
  • I modified the query like `select * from (SELECT DATE_PART('month' ,created_ts)||'/'||DATE_PART('day' ,created_ts)||'/'||DATE_PART('year' ,created_ts) as created_ts from ROYALTY_STG.STG_DL_FILES ) A where Not(split_part(created_ts,'/',1) between '01' and '12' and split_part(created_ts,'/',2) between '01' and '31' and split_part(created_ts,'/',3) between '2000' and '9999')` . The month part is not fetching any records between 01 and 12. – Midhun Aug 23 '16 at 13:32