SQL Using Between and "Date Fields" 1

[rstitzel]

I have an accounts receivable file. The file has a field for storing an invoice date. But it's a NUMERIC 6 digit field (used to store in MMDDYY format). I want to use a sql statement to pull a date range using BETWEEN but I don't think I'll get the results I'm looking for as this field is a NUMBER and NOT a date.

SELECT * FROM arfile WHERE InvDate BETWEEN StartDate AND EndDate

Two Questions:

1. Can you put an actual DATE and/or TIME field in a physical file. I only see where you can create a signed, packed or alpa field.

2. If you can't put in a actual date field. How do you got about retrieving records based on a date range from numeric data?

Any and all help is appreciated!!

Thank you.

 

[Mercury2]

Hi rstitzel,
Indeed you can put an actual DATE and/or TIME field in a physical file as below :

A            MYDATE          [b][COLOR=red]L[/color][/b]         COLHDG('My USA date')
A                                      DATFMT(*USA)         
A            MYTIME          [b][COLOR=red]T[/color][/b]         COLHDG('My USA time')
A                                      TIMFMT(*USA)      
A            MYDTS           [b][COLOR=red]Z[/color][/b]         COLHDG('My Timestamp')

Column "Data Type":
Type "L" to indicate Date, "T" to indicate Time and "Z" to indicate Date And TimeStamp data:

Name                                     Data      Decimal        
Type       Name        Ref     Length    Type     Positions    Use
         MYDATE                           L                       
Functions                                                         
COLHDG('My USA date')

(u]Procedure[/u]:
Create a new PF with new date fields and convert the old file ARFILE from MMDDYY to *USA date format (MM/DD/YYYY) with a single SQL statement. Beneath I show how you can use SQL to convert from numeric MMDDYY format to *USA date format:

Insert Into NewFile 
Select Fld1, Fld2, ..., 
Substr(Digits(InvDate), 1, 2) 
Concat '/' 
Concat Substr(Digits(InvDate), 3, 2) 
Concat '/' 
Concat '20' 
Concat Substr(Digits(InvDate), 5, 2)
, ..., FldN
From ARFILE

Of course you need to fit this SQL stm to your needs.
You could also write a little RPG IV program to do the conversion.
Then f.e. you can type the sql stm like this:

SELECT * FROM NewFile WHERE InvDate BETWEEN '07/01/2004' AND '07/31/2004'

HTH -- Philippe

 

[rstitzel]

You're post was very helpfull. Like I mentioned, I've been using LANSA to create/maintain my fields/files. When I created the file with a date field (as you described above) in DDS and then brought it into LANSA, LANSA converted it to an alpha field!?

I'm still fairly new to programming in RPG (1.5 years)Knowing that I can use date and timestamp fields will be very helpfull and will save me some coding, since I won't have to convert my date/times to numeric before writing to my files.

So THANK YOU!!