RE: [sqr-users] RE: Date Validation

["Chu, Nicholas" <Nicholas.Chu@RedPrairie.com>]

I guess you can also using oracle's to_date function to validate the date
too.

select to_date('12/55/2004','MM/DD/YYYY') from dual; (this will give you
error because of invalid day)

put that in a procedure and use on-error to return error messages.

that would be the simplest way i think.

nicholas

-----Original Message-----
From: sqr-users-bounces+nicholas.chu=redprairie.com@sqrug.org
[mailto:sqr-users-bounces+nicholas.chu=redprairie.com@sqrug.org]On
Behalf Of Alexander, Steve
Sent: Wednesday, June 16, 2004 11:15 AM
To: 'This list is for discussion about the SQR database reportinglangu
age from Hyperion Solutions.'
Subject: RE: [sqr-users] RE: Date Validation 


While SQR has some built-in date functions, they "validate" dates by
aborting your program.  If you have a six digit date, you could do as
follows:

read 1 into ... $mmddyy:6 ...

if translate($mmddyy, '0123456789', '') <> ''
  show $mmddyy ' has a non-digit character'
else
  let #mm = substr($mmddyy, 1, 2)
  let #dd = substr($mmddyy, 3, 2)
  evaluate #mm
    when < 1
    when > 12
      show $mmddyy ' has a bad month'
    when = 2
      if #dd < 1 or #dd > 29
        show $mmddyy ' has a bad day'
      end-if
      if mod(to_number(substr($mmddyy, 5, 2)), 4) > 0 and #dd > 28
        show $mmddyy ' has a bad day'
      end-if
    when = 4
    when = 6
    when = 9
    when = 11
      if #dd < 1 or #dd > 30
        show $mmddyy ' has a bad day'
      end-if
    when-other
      if #dd < 1 or #dd > 31
        show $mmddyy ' has a bad day'
      end-if
  end-evaluate



-----Original Message-----
From: Krishna [mailto:knookula@yahoo.com]
Sent: Wednesday, June 16, 2004 7:42 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] RE: Date Validation 


Hi all, does anyone have suggestions for validating a set of dates reading
from an input data file in SQR. I am looking for faster and less resource
intensive method of validating dates when I read from the input file. The
input file which I read will have 6 columns of date field and before I use
these values in my processing I would like to validate the dates. Size of
Input file will have around 2-3 million records. 
Your input will be very helpfull. 

                
---------------------------------
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