Re: SQR and date formatting

[John Milardovic <milardj@SX.COM>]

A couple of problems I see right off the bat:

1.  Your where clause uses $begin_date and $end_date but your input
statement is for $beginning and $ending.

2.  You are taking a 9 byte substring of your variable - which will give you
(MM/DD/YYY) - and which is not needed in the first place -  shouldn't you be
taking a substr of the column (make it the lvalue) and compare it against
the input date (no substr needed - make it the rvalue).

HTH
John
> -----Original Message-----
> From: RUFFINGT [SMTP:RUFFINGT@OAKWOOD.ORG]
> Sent: Thursday, April 06, 2000 11:04 AM
> To:   SQR-USERS@list.iex.net
> Subject:      SQR and date formatting
>
> Hello,
>
> I'm a 'newbie' to SQR (have 'no' official training in sqr) but have a
> slight
> problem with my 'where clause'.  I am trying to select a date from a table
> and compare that date with a(n) inputted date.  The sqr works (no errors),
> but I don't get any output based on the comparison of the dates.  I have
> tried to_char, to_date and anything else I might find out there, but
> nothing
> seems to be working correctly for me.  I have copied the program below:
>
> input $operator 'Operator ID to Report on'
>       input $beginning 'Date to Begin Report on (MM/DD/YYYY)' type=date
> format='MM/DD/YYYY'
>       input $ending 'Date to End Report on (MM/DD/YYYY)' type=date
> format='MM/DD/YYYY'
>       display $operator
>       display $beginning
>       display $ending
>
>     select statement:
> begin-procedure Report          ! Main report processing
> begin-SELECT
> PRCSINSTANCE
> JOBINSTANCE
> PRCSJOBNAME
> PRCSTYPE
> PRCSNAME
> OPRID
> RUNLOCATION
> RUNDTTM
> TO_CHAR (BEGINDTTM,'MM/DD/YYYY') &BEGINDTTM
> TO_CHAR (BEGINDTTM, 'HH:MM:SS') &HRDTTM
> TO_CHAR (ENDDTTM,'MM/DD/YYYY') &ENDDTTM
> TO_CHAR(ENDDTTM, 'HH:MM:SS') &ENDHRTM
> RUNSTATUS
> MSGSET
> MSGNUM
>   let $locations = &RUNLOCATION
>   do select-locations
>   do if-runstatus
>   do get-message
>
>
>
>    move &PRCSINSTANCE    to $field1
>    move &JOBINSTANCE     to $field2
>    move &PRCSJOBNAME     to $field4
>    move &PRCSTYPE        to $field5
>    move &PRCSNAME        to $field6
>    move &OPRID           to $field7
>    move &BEGINDTTM       to $field11
>    move &HRDTTM          to $field14
>    move &ENDDTTM         to $field13
>    move &ENDHRTM         to $field15
>
>
>    print $field1         (+1,{C_PRCSINSTANCE})
>    print $Field2         (+0,{C_JOBINSTANCES})
>    print $Field4         (+0,{C_PRCSJOBNAME})
>    print $Field5         (+0,{C_PRCSTYPE})
>    print $Field6         (+0,{C_PRCSNAME})
>    print $Field7         (+0,{C_OPRID})
>    print $field8         (+0,{C_RUNLOCATION})
>    print $Field11        (+0,{C_BEGINDTTM})
>    print $field14        (+0,{C_HRDTTM})
>    print $field13        (+0,{C_ENDDTTM})
>    print $field15        (+0,{C_ENDHRTM})
>    print $field12        (+0,{C_RUNSTATUS})
>    print $field16        (+0,{C_MSGLINE})
>
>
>  FROM PSDBA_PRCSRQST
>   WHERE !PRCSJOBSEQ = 0
>   OPRID = substr($operator,1,7)
>   and substr($Begin_Date,1,9) >= BEGINDTTM
>   and substr($End_Date,1,9) <= ENDDTTM
>  ORDER BY PRCSINSTANCE DESC
> end-SELECT
> end-procedure
>
> Any help would be GREATLY appreciated at this point!
>
> Thanks,
>
> Todd Ruffing