Re: SQL Between

[Billy Rowe <billyrowe@xxxxxxx>]

tony.richardson@xxxxxxxxxxxx wrote:
> I need to run an SQL BETWEEN to compare some date fields.
> The problem Iâm having is our dates are stored in separate
> fields (century, year, month, and day). I've tried to concatenate
> the fields together, but that doesn't seem to work.
> Anyone out there done this before?

This an working example of creating a DATE type column for display and 
selection from a numeric column, the only difference is you wouldn't
have to sub string since you say your columns are already separate.

Select Cust_Id, Inv_Id,
-- Create Invoice Date as ISO date: YYYY-MM-DD
cast(SubStr( Char( Inv_Date ), 1, 4 ) || '-' ||
     SubStr( Char( Inv_Date ), 5, 2 ) || '-' ||
     SubStr( Char( Inv_date ), 7, 2 ) as Date ) as InvDate,

-- Create Invoice Due Date as ISO date: YYYY-MM-DD
cast( SubStr( Char( Inv_Due_Date ), 1, 4 ) || '-' ||
      SubStr( Char( Inv_Due_date ), 5, 2 ) || '-' ||
      SubStr( Char( Inv_Due_Date ), 7, 2 ) as Date ) as InvDueDate,

-- Get invoice age in days
Days( current date ) - Days(
 Cast( SubStr( Char( Inv_Due_Date ), 1, 4 ) || '-' ||
       SubStr( Char( Inv_Due_Date ), 5, 2 ) || '-' ||
       SubStr( Char( Inv_Due_Date ), 7, 2 ) as Date ) ) as InvAge,

( Inv_Amt-Inv_Pay_Amt) as InvTotal from Ar_Trans
Where ( Inv_Amt-Inv_Pay_Amt ) > 0
 and  Days( current date ) - Days(
 Cast( SubStr( Char( Inv_Due_Date ), 1, 4 ) || '-' ||
       SubStr( Char( Inv_Due_Date ), 5, 2 ) || '-' ||
       SubStr( Char( Inv_Due_date ), 7, 2 ) as Date ) ) >= 30
Order By Cust_Id, Inv_Id, InvDueDate;

HTH,
Billy