Re: Date functions in SQL/400

[Rob Berendt <rob@xxxxxxxxx>]

You need to do some manipulation on that number.  It is assuming that 
date_field is the number of days from January 1, 0001.

Try the following:
Select ....
where DATE(('19' concat (SUBSTR(DIGITS(date_field),2,2) concat '-' concat 
(substr(digits(date_field),4,2) concat '-' concat 
(substr(digits(date_field),6,2)) = current date

>From the SQL manual:
3.2.17 DATE
 
+------------------------------------------------------------------------+
¦                                                                        ¦
¦ >--DATE--(--expression--)--------------------------------------------> ¦
¦                                                                        ¦
+------------------------------------------------------------------------+
The DATE function returns a date from a value.
 
The argument must be a timestamp, a date, a positive number less than or equal 
to 3652059, a valid string 
representation of a date, or a character string of length 7.
 
If the argument is a character string of length 7, it must represent a valid 
date in the form yyyynnn, where 
yyyy are digits denoting a year, and nnn are digits between 001 and 366 
denoting a day of that year.
 
The result of the function is a date.  If the argument can be null, the result 
can be null; if the argument is null, 
the result is the null value.
 
The other rules depend on the data type of the argument:
 
$   If the argument is a timestamp:
 
    The result is the date part of the timestamp.
 
$   If the argument is a date:
 
    The result is that date.
 
$   If the argument is a number:
 
    The result is the date that is n-1 days after January 1, 0001, where n is 
the integral part of the number.
 
$   If the argument is a character string:
 
    When a string representation of a date is SBCS data with a CCSID that is 
not the same as the default CCSID 
    for SBCS data, that value is converted to adhere to the default CCSID for 
SBCS data before it is interpreted 
    and converted to a date value.
 
    When a string representation of a date is mixed data with a CCSID that is 
not the same as the default CCSID 
    for mixed data, that value is converted to adhere to the default CCSID for 
mixed data before it is interpreted 
    and converted to a date value.

 $   The following DATE scalar function applied to an ISO string representation 
of a date:
 
         DATE('1988-12-25')
 
    Results in an internal representation of '1988-12-25'.
 
$   The following DATE scalar function applied to an EUR string representation 
of a date:
 
         DATE('25.12.1988')
 
    Results in an internal representation of '1988-12-25'.
 
$   The following DATE scalar function applied to a positive number:
 
         DATE(35)
 
    Results in an internal representation of '0001-02-04'.




ravi@spacestar.com on 10/06/98 12:06:36 PM
Please respond to MIDRANGE-L@midrange.com@Internet
To:     MIDRANGE-L@midrange.com@Internet
cc:      
Subject:        Date functions in SQL/400

Hello,

I have a field in a file P(7,0) which contains dates in YYMMDD format. I
want to retrieve records from this file where the date is equal to the
current date.

I tried:
 select * from file where date_field = current date

I get the error "operands not compatible"

The I tried:

select * from file where date(datefield) = current date
No records were returned. I know there are valid records. 

So I checked the returned value:
select date(datefield) from file.

The result is a number of rows with '+++++++++', which means the data is
not valid. I looked up the manual and the date function can be used
against a +ve number < or = 3652059. 
Am I getting this error because the data type of the date field is
Packed? What are my other options to select records where date_field
equals current date? I will be running this statement in RPG/400 &
embedded SQL. Our system date format is MDY. Does this mean I cannot use
current date function to compare against dates in YYMMDD format? Can I
do soemthing to manipulate data? 

Any pointers will be appreciated...

Thanks in advance.

Ravi
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---