RE: Date functions in SQL/400

There's a date format parameter, associated with ISQL session or CRTSQL*
commands.
This format should correspond with date format in the database field,
otherwise SQL will not interpret date properly.


    Best regards,

          Alexei Pytel



> -----Original Message-----
> From: Ravi [SMTP:ravi@spacestar.com]
> Sent: Tuesday, October 06, 1998 5:14 PM
> To:   MIDRANGE-L@midrange.com
> 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
+---