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 ...
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
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.
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.
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
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
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.
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
The below query will work for ur scenario.
WHEN B.calendar_date IS NULL
THEN 'Invalid Date'
FROM invalid_dates A
( (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.
I wrote this some time ago as a SQL UDF to check for valid integer dates YYYYMMDD:
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