Date compare in SQL

[arrow483]

I would like to do a date compare in a SQL view.


Case When ORDER_DATE < CURRENT DATE
Then ......

Problem is: ORDER_DATE is a numeric *CYMD field (like 1050613), and CURRENT DATE comes in as a MDY Date field. How can I convert one or the other to get a valid comparison?

[jimmyoctane]

Re: Date compare in SQL

Here is a article I "found" the other day

SQL Date Support 



Raw Data

Order No.        Ship Date
     1           19,970,620
     2           19,970,907
     3           19,970,824
     4                    0
     5           19,970,927
     6                    0
     7           19,970,929
     8                    0
     9           19,970,903
    10                    0




  This query gives us a more readable date 
  Recall from last month that the Digits 
  function converts a numeric expression 
  to a character string. Itâ??s necessary 
  to use the Digits function here because 
  we canâ??t perform character-string operations 
  such as concatenation on numeric columns 
  (e.g., OhShDt). Also recall that the Substr 
  function extracts the portion of the character 
  string named in the first argument, starting in 
  the position specified in the second argument, 
  for the length specified in the third argument. 
  The concatenation operator adds a hyphen to the 
  first and second substrings and connects the 
  three substrings to form one long string.

  

Select OhNum,
   Substr( Digits( OhShDt ), 1, 4 )|| '-' ||
   Substr( Digits( OhShDt ), 5, 2 )|| '-' ||
   Substr( Digits( OhShDt ), 7, 2 )
     As ShipDate
  From OrdHdr

 


This query produces these results:

Order No.  Ship Date
     1     1997-06-20
     2     1997-09-07
     3     1997-08-24
     4     0000-00-00
     5     1997-09-27
     6     0000-00-00
     7     1997-09-29
     8     0000-00-00
     9     1997-09-03
    10     0000-00-00





  SQL has strong support for native date, time, and timestamp data types. 
  To see how to take advantage of SQLâ??s date support, 
  letâ??s start with another numeric column in the OrdHdr table called 
  OhDtOr (order date), which is stored in the same packed 8,0 format 
  as OhShDt (YYYYMMDD). To calculate an estimated ship date, 
  we use the Date function to convert the numeric order dates 
  to native, or true, dates and then use SQLâ??s date support to 
  add 10 days to the order dates (SQL lets us add durations 
  only to date fields, not to numeric or character values):
 

Select OhNum,
   Substr( Digits( OhDtOr ), 1, 4 )|| '-' || 
   Substr( Digits( OhDtOr ), 5, 2 )|| '-' || 
   Substr( Digits( OhDtOr ), 7, 2 ) 
   As OrderDate, 
   Date( Substr( Digits( OhDtOr ), 1, 4 ) 
    || '-' || 
      Substr( Digits( OhDtOr ), 5, 2 ) 
       || '-' || 
      Substr( Digits( OhDtOr ), 7, 2 ) ) 
   + 10 Days As EstShipDate 
  From OrdHdr



This query produces these results:



Order No.  Order Date   EstShipDa
     1     1997-06-15   06/25/97
     2     1997-09-01   09/11/97
     3     1997-08-15   08/25/97
     4     1997-09-29   10/09/97
     5     1997-09-20   09/30/97
     6     1997-09-28   10/08/97
     7     1997-09-18   09/28/97
     8     1997-09-25   10/05/97
     9     1997-08-24   09/03/97
    10     1997-09-27   10/07/97



The third entry in the Select clause uses the Date 
function to return a native date rather than a 
character string. In this case, the Date function 
has a complex character string expression as its argument; 
however, it can take several other types of arguments. 
When the Date functionâ??s argument is a character string, 
the string must be in one of several valid date formats. 
Because the ISO format used in the query is one of the valid 
formats, the Date function returns a native date representing 
the order date.  Notice how I added 10 days to the native 
date by specifying a value of 10 and a duration of Days. 
You can add other values. For example, to calculate a purge 
date, I can add 1 year, 2 months, and 5 days to the order date 
as follows:


Select
  OhNum,
  Date('
   Substr( Digits( OhDtOr ), 1, 4 ) || '-'|| 
   Substr( Digits( OhDtOr ), 5, 2 ) || '-'|| 
   Substr( Digits( OhDtOr ), 7, 2 ) ) 
  + 1 Year + 2 Months + 5 Days 
    As PurgeDate 
  From OrdHdr

I guess the easiest way to work (if possible) pull off the century.

Jimmy

[pjk]

Re: Date compare in SQL

Hmm... Might be easier to convert the CURRENT DATE to match the format of the ORDER_DATE. Assuming CURRETN DATE = numeric 061405 ==> ('1' || substring(digits(CURRENT DATE), 5,2)) || substring(digits(CURRENT DATE), 1, 4))) Unless you plan on going back in time, I think it's safe to set '1' for century always for CURRENT DATE--if not, you'll have to throw a CASE on there to compare year.

[jamief]

Re: Date compare in SQL

Thank God you showed up
Im a bit light on the SQL

Could you throw up a CASE example (if YY > 50) then '19' else '20'

Please

Thanks
Jamie

[pjk]

Re: Date compare in SQL

(case when substring(digits(CURRENT DATE), 5, 2) > '80' then '0' else '1' end)

This assumes that any year great than 80 should be 19xx and less than 20xx (btw, this doesn't work well with birthdays)

[jamief]

Re: Date compare in SQL

CYYMMDD to *ISO
1050615 to 06152005 Thanks Patrick (see you can teach an old dog new tricks )

select IHPART, IHMNTH || '/' || IHDAY || '/' || CASE when IHCNCD = '0' THEN '19'
else '20' END || IHYEAR as DATE from INVHST

[arrow483]

Re: Date compare in SQL

Thanks. I was able to get it to work with a variation of pjk's method. It seems that if I select CURRENT DATE I get back 06/15/05, but if I select %char(CURRENT DATE) I get back 2005-06-15. (This must be a feature). So the statement works:

CASE WHEN
  DUEDATE < ('1' || substring(char(CURRENT DATE), 3,2)  ||
   substring(char(CURRENT DATE), 6, 2) ||
   substring(char(CURRENT DATE), 9,2))
  THEN 'OVERDUE'
 ELSE  'NOT_DUE'
 END AS DATESTS

In this case, I don't have to worry about pre-Y2K dates.