RE: [sqr-users] RE: Date Validation

["Sapitan, Mary Ann (MG)" <MSapitan@dow.com>]

This code also checks for leap year; I also added flags you can use in other 
procedure (when $Valid_Dt_Flag = 'Y', continue processing).  I'm just not sure 
if this will be faster or slower than the other pieces of code everyone has 
suggested. 


*******************************************
begin-procedure VALIDATE-DATE

    move 'Y' to $Valid_Dt_Flag

! Depending on the Date format of your input file, parse the date to the 
following numeric variables
! #Month, #Day, #Year 

    ! Validate the Month
    if #Month = 0 or #Month > 12
        move 'N' to $Valid_Dt_Flag
    end-if

        ! Validate Day of the Month if Month is valid
    if $Valid_Dt_Flag = 'Y'
        if #Month = 2
            move 28 to #MonthDays
            if (((mod(#Year,4) = 0) and (mod(#Year,100) != 0)) or        ! Leap 
Year check
                 (mod(#Year,400) = 0))
                add 1 to #MonthDays
            end-if
        else
            if #Month = 4 or #Month = 6 or #Month = 9 or #Month = 11
                move 30 to #MonthDays
            else
                move 31 to #MonthDays
            end-if
        end-if
    end-if

    if #Day = 0 or #Day > #MonthDays
        move 'N' to $Valid_Dt_Flag
    end-if

   if $Valid_Dt_Flag = 'Y'
        [Continue Processing]
   end-if


end-procedure VALIDATE-DATE
*******************************************

Hope this helps! :)


Thanks,
Ma-an 



-----Original Message-----
From: Alexander, Steve [mailto:Steven.Alexander@sanjoseca.gov] 
Sent: Saturday, June 19, 2004 1:32 AM
To: 'This list is for discussion about the SQR database reportinglangu age from 
Hyperion Solutions.'
Subject: RE: [sqr-users] RE: Date Validation 


Don't forget that month and day cannot be zero and none of the 2 character 
strings may include a minus sign or decimal point.  Also, testing day against 
31 is a good way to catch some invalid dates, but to catch them all, we have to 
test for the actual last day of each month, including leap year February.  I 
wrote the appropriate code a few days ago.

Another possibility is to set up an array or a string of 1231 positions. Map a 
0, 1, or 2 into each position based on whether the corresponding date is 
invalid (May 32 = 532), valid (June 28 = 628), or possibly valid (Feb 29 = 
229).  Extract the month and day in a single variable, $mmdd, and proceed as 
follows:

move $mmdd to #mmdd
if translate($mmdd, '0123456789', '') <> '' 
or range(#mmdd, 101, 1231) = 0 
or valid.day(#mmdd) = 0 
or (#mmdd = 229 and mod(#year, 4) > 0)
  show $mmddyy ' is a bad date'
end-if

-----Original Message-----
From: Knapp, Richard [mailto:KnappR@umsystem.edu]
Sent: Friday, June 18, 2004 9:29 AM
To: This list is for discussion about the SQR database reportinglanguage fr 
omHyperion Solutions.
Subject: RE: [sqr-users] RE: Date Validation 



I haven't been paying much attention to this thread so if I'm repeating 
something that has already been said, please forgive me.  

If the dates are 6 characters - 2 for the day, 2 for the month and 2 for the 
year, this is cake.  Split up the string and convert the 2 char parts to 
numbers.  If all parts convert without error (no letters or special chars), 
then test that the day is <= 31, test that the month <= 12 and forget the year 
because any value will be OK.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu


-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of 
Alexander, Steve
Sent: Friday, June 18, 2004 10:53 AM
To: 'This list is for discussion about the SQR database reportinglangu age from 
Hyperion Solutions.'
Subject: RE: [sqr-users] RE: Date Validation 

Krishna, it was unclear whether you said there were 6 dates in each line of the 
input file or there were six characters in each date and one date one each line 
of the input file.  Also, are the dates within a limited range of time?  If the 
dates are six characters (no century) then there are only 36525 valid values 
repeated over and over for millions of dates.

Unfortuneately, SQR has no built-in date validation function, but there may be 
programming tricks that are simpler than the code I sent you.  What can you 
tell us about the dates?  How are they formatted and is there any restriction 
to the values?



-----Original Message-----
From: James Womeldorf [mailto:jwomeldo@fastenal.com]
Sent: Friday, June 18, 2004 8:31 AM
To: 'This list is for discussion about the SQR database reporting languagef rom 
Hyperion Solutions.'
Subject: RE: [sqr-users] RE: Date Validation 


Hi Krishna,
If there are a relatively small number of actual dates represented in the file 
(lots of duplicates) perhaps you could store validated dates in a string which 
delimits them with some odd character that you would NEVER find in the date 
field.  Then, as a first test use INSTR() to see if a date is in the validated 
list.  If not there, then check using Oracle.  It should cut the number of 
database calls drastically.  
The only thing you would have to watch is that the validation string length did 
not exceed the maximum string length for SQR (32767).  If the string length 
would be exceed then just avoid adding any more dates to it. Jim Womeldorf



-----Original Message-----
From: Krishna [mailto:knookula@yahoo.com]
Sent: Friday, June 18, 2004 9:33 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] RE: Date Validation 


Well, currently I am validating all the dates from the input file by sending 
the dates one at a time to Oracle and checking them whether the dates are 
valid. If the the date is invalid then I am trapping the invalid date from the 
ON-ERROR procedure. 
   I am trying to see if there are any other better method of date validations 
in SQR. Number of records in my input file like I said earlier is 2-3 million. 
The way I am currently achieving the task is by sending a request to Oracle six 
times one for each date, since I have to write an error log specifying which 
date is invalid in the input file for every record read from the input file. 

                
---------------------------------
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users