To find invalid dates?

Database
Enthusiast

To find invalid dates?

I have dates stored in decimal format, i need to weed out the invalid dates. I need to select only the rows which has the valid dates. I tried the solution given in this link http://forums.teradata.com/forum/enterprise/need-to-check-format-to-yyyy-mm-dd. But the problem is, i only need to weed out the invalid dates no matter what the date is, For example 99991231 is a valid date to me.

Format : YYYYMMDD

EX for Valid date : 20030315

EX for Invalid Date: 19608030

thanks in advance ...

Tags (1)
10 REPLIES
Enthusiast

Re: To find invalid dates?

I think 9999 and 4712 might be the only exception not present in syscalendar you can perhaps manually override them .

I do not suppose any database maintains valid data outside this century

Enthusiast

Re: To find invalid dates?

You can also ofcourse use substring and check validity of year,month and day separately ,where you will have ot use case statements to check max value of allowed day.

Supporter

Re: To find invalid dates?

remains the problem of 29 Feb validity checking...

I guess 99991231 is a high value. Your might also have some other speccial dates.

What is the standard range of dates?

maybe a two step approach might work.

check the special dates like 99991231 visually and exclude them in the check.

Use the sys_calendar.calendar join for all others.

other way to do - create a table which contains all dates up to 99991231 and do a left outer join.

Supporter

Re: To find invalid dates?

create volatile table ref_date as (
select row_number() over (order by c1.calendar_date, c2.calendar_date) as id,
('0001-01-01' (date)) + (id -1) as valid_date
from sys_calendar.Calendar c1
cross join
sys_calendar.Calendar c2
where c2.calendar_date - current_date between 1 and 50
qualify id < 3652060
) with data
unique primary index (valid_date)
on commit preserve rows

might do the trick

Enthusiast

Re: To find invalid dates?

This is an excellent query..but I have few questions :

1) why we are doing this

c2.calendar_date - current_date between 1 and 50

2) how do you find the upper value of valid date 3652060

3) Is it necessary to add calendar_date twice in the select query to form ID value.

Supporter

Re: To find invalid dates?

1.) calendar tables has < 40000 rows. We are heading for 3652060 rows. So 50 is the min value where times the count(*) calendar > 3652060. - we would clearly minimise unneccesary work.

2. max date - min date

3. no

Enthusiast

Re: To find invalid dates?

Hi Sachin,

The below query will work for ur scenario.

SELECT A.jdate,

CASE

WHEN B.calendar_date IS NULL

THEN 'Invalid Date'

ELSE'Valid Date'

END Status

FROM invalid_dates A

LEFT JOIN

sys_calendar.calendar B

ON

( (CAST(SUBSTR(CAST(A.jdate AS CHAR(8)),1,4 )AS INTEGER)-1900) *10000 +

CAST(SUBSTR(CAST(A.jdate AS CHAR(8)),5,2 )AS INTEGER) *100 +

CAST(SUBSTR(CAST(A.jdate AS CHAR(8)),7,2 )AS INTEGER) ) =CAST(B.calendar_date AS INTEGER)

Let me know if you are not clear.

thanks,

Venkat

Junior Contributor

Re: To find invalid dates?

I wrote this some time ago as a SQL UDF to check for valid integer dates YYYYMMDD:

  CASE 
WHEN (d / 10000 BETWEEN 1900 AND 9999) -- correct year, adjust to your minimum date
AND (d / 100 MOD 100 BETWEEN 1 AND 12) -- correct month
AND ((d / 100 MOD 100 IN (1,3,5,7,8,10,12) AND d MOD 100 BETWEEN 1 AND 31)
OR (d / 100 MOD 100 IN (4,6,9,11) AND d MOD 100 BETWEEN 1 AND 30)
OR (d / 100 MOD 100 = 2 AND d MOD 100 BETWEEN 1 AND 28
+ CASE WHEN (((d / 10000) MOD 4 = 0) AND ((d / 10000) MOD 100 <> 0))
OR ((d / 10000) MOD 400 = 0) THEN 1 ELSE 0 -- leap year?
END)) -- day between 1 and 28/29/30/31
THEN 1 ELSE 0
END AS ValidDate

Dieter

Enthusiast

Re: To find invalid dates?

Thanks for your replies :)