SQL - Finding greatest date - NON ISO

SQL - Finding greatest date - NON ISO

Post by Craig Crocke » Fri, 11 Jul 2003 23:39:59



I am looking for a way to find the greatest date for a group of records in SQL. The date is 6 digit numeric and the century is 1 numeric. I want to retrive the last record or the greatest date.

  Item          Type              Century             Date  
 10224           1                    0                98/11/09
 10224           1                    0                98/11/27
 10224           1                    0                98/12/02
 10224           1                    0                98/12/09
 10224           1                    0                99/02/11
 10224           1                    0                99/04/09
 10224           1                    0                99/04/13
 10224           1                    0                99/05/03
 10224           1                    0                99/05/07
 10224           1                    0                99/06/04
 10224           1                    0                99/08/24
 10224           1                    1                 0/02/14
 10224           1                    1                 0/04/04
 10224           1                    1                 0/09/13
 10224           1                    1                 0/09/14  

 
 
 

SQL - Finding greatest date - NON ISO

Post by Jonathan Bal » Sat, 12 Jul 2003 00:40:24


Because your century digit apparently is separate from
the rest of the date field, you're going to need to
prepend it to the date.  You can do this "on the fly",
so to speak, as follows (I have used a prefix for field
names, as the word "date" is a SQL reserved word):

    char(i_Century) || digits(i_Date)

Thus, you would obtain the row(s) containing the
highest date as follows:

    select *
      from my_table
     where char(i_Century) || digits(i_Date) =
       (select max(char(i_Century) || digits(i_Date))
          from my_table)

You might want to consider writing your own SQL
function that performs the recasting and concatenation,
in order to make the code in your SELECT statement a
little easier to read.  For example, you could write a
SQL function called 'make_date' that takes the century
and date fields as inputs, recasts them to character
values, concatenates them, and returns a 7 byte
character string (that's what 'char(i_Century) ||
digits(i_Date)' does).  Then, you would change your
SELECT to read:

    select *
      from my_table
     where make_date(i_Century, i_Date) =
       (select max(make_date(i_Century, i_Date))
          from my_table)

There's a bit of a performance hit to doing that, but
not much.

As an alternative, you could create a view over your
table that has the date formatted in this way:

    create view my_view
       (item, i_Type, i_ISO_Date) as
    select item, type,
           char(i_Century) || digits(i_Date)
      from my_table

Then, your the SELECT statement in your business
application code would be simpler still:

    select *
      from my_view
     where i_ISO_Date =
       (select max(i_ISO_Date)
          from my_view)


> I am looking for a way to find the greatest date for a group of records
> in SQL. The date is 6 digit numeric and the century is 1 numeric. I want
> to retrive the last record or the greatest date.

>   Item          Type              Century             Date  
>  10224           1                    0                98/11/09
>  10224           1                    0                98/11/27
>  10224           1                    0                98/12/02
>  10224           1                    0                98/12/09
>  10224           1                    0                99/02/11
>  10224           1                    0                99/04/09
>  10224           1                    0                99/04/13
>  10224           1                    0                99/05/03
>  10224           1                    0                99/05/07
>  10224           1                    0                99/06/04
>  10224           1                    0                99/08/24
>  10224           1                    1                 0/02/14
>  10224           1                    1                 0/04/04
>  10224           1                    1                 0/09/13
>  10224           1                    1                 0/09/14  


 
 
 

1. Sql *ISO date format

I have to compile SQL programs with datfmt(*ISO).  Is there a way to put
a keyword in the source to specify it, or can you only do it by
prompting when you compile and manually changing the date format.

In the 'H' spec there is a keyword datfmt, but this doesn't work as it
is not used at compile time.

Please help
Thanks

2. Mac Hard FIles

3. RPG date format problem from *ISO to dd/mm/yyyy.

4. Margin Rules

5. Converting *ISO date to YYYYMMDD....

6. NYC April 22 OMG / Corba / BOCA / UML / MOF

7. *ISO Date: CPYF will not convert-why?

8. Opinion?

9. ISO Date type

10. SQL to convert numeric (8.0) into ISO dates

11. restoring data from a non IBM non unix machine !

12. Trouble with Embedded Non-Select SQL in ILE RPG

13. Changing 'non fields' through SQL