All Forums Database
barani_sachin 141 posts Joined 01/12
04 Jun 2012
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 ...

 

Jigar 70 posts Joined 09/11
06 Jun 2012

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

Jigar 70 posts Joined 09/11
06 Jun 2012

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.

ulrich 816 posts Joined 09/09
06 Jun 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
06 Jun 2012
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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

S_Banerjee 15 posts Joined 02/09
07 Jun 2012

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.

ulrich 816 posts Joined 09/09
07 Jun 2012

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

venkylingutla 19 posts Joined 06/12
08 Jun 2012

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

dnoeth 4628 posts Joined 11/04
09 Jun 2012

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

Dieter

barani_sachin 141 posts Joined 01/12
11 Jun 2012

Thanks for your replies :)

kw 8 posts Joined 05/09
24 Jul 2012

If you are able to install UDFs at your site then the isdate UDF can be used to validate 20 different date formats :  http://www.teradataforum.com/isdate.htm

 

 

You must sign in to leave a comment.