Tim has offered to create an EDB webpage / mini site showing searchable EDB SQL Scripts ... when he has the time to get round to building it

/* between EDB v3.0 & getting EWB v1.0 up & running I', not sure when that will be */

Here is a first (pretty basic) FUNCTION I would like to offer to post up on the examples site when it is ready.

It takes 2 timestamps (start & end), plus a boolean for whether hours / minutes are important, and converts them into a string expressing the period covered in the form:

'from Monday 1st Jan 2011 to Wednesday 3rd Jan 2011'

I'm not sure whether the SQL is exactly optimal ... there may be easier ways to write some of it, but it does work.

*** Please feel free to use it & post other nice examples on this thread for us all to look at:

--

CREATE FUNCTION "ExpressDateRangeNicely" (IN aStartDate TIMESTAMP, IN aEndDate TIMESTAMP, IN aWithTimes BOOLEAN)
RETURNS VARCHAR(100) COLLATE ANSI
BEGIN                                    
 DECLARE RESULT VARCHAR(100);
 DECLARE YEARStartNum INTEGER;
 DECLARE YEAREndNum INTEGER;
 DECLARE MONStartNum INTEGER;
 DECLARE MONEndNum INTEGER;
 DECLARE DAYStartNum INTEGER;
 DECLARE DAYEndNum INTEGER;
 DECLARE DOWStartNum INTEGER;
 DECLARE DOWEndNum INTEGER;
 DECLARE HOURStartNum INTEGER;
 DECLARE HOUREndNum INTEGER;
 DECLARE MINStartNum INTEGER;
 DECLARE MINEndNum INTEGER;

 DECLARE DOWStart VARCHAR(9);
 DECLARE DOWEnd VARCHAR(9);
 DECLARE DAYStart VARCHAR(4);
 DECLARE DAYEnd VARCHAR(4);
 DECLARE MONStart VARCHAR(3);
 DECLARE MONEnd VARCHAR(3);
 DECLARE MINStart VARCHAR(2);
 DECLARE MINEnd VARCHAR(2);

SET YEARStartNum = EXTRACT(YEAR FROM aStartDate);
SET YEAREndNum = EXTRACT(YEAR FROM aEndDate);
SET MONStartNum = EXTRACT(MONTH FROM aStartDate);
SET MONEndNum = EXTRACT(MONTH FROM aEndDate);
SET DAYStartNum = EXTRACT(DAY FROM aStartDate);
SET DAYEndNum = EXTRACT(DAY FROM aEndDate);
SET DOWStartNum = EXTRACT(DAYOFWEEK FROM aStartDate);
SET DOWEndNum = EXTRACT(DAYOFWEEK FROM aEndDate);
SET HOURStartNum = EXTRACT(HOUR FROM aStartDate);
SET HOUREndNum = EXTRACT(HOUR FROM aEndDate);
SET MINStartNum = EXTRACT(MINUTE FROM aStartDate);
SET MINEndNum = EXTRACT(MINUTE FROM aEndDate);

IF MinStartNum BETWEEN 0 AND 9 THEN
 SET MINStart = '0'+CAST(MinStartNum as CHAR(1));
 ELSE
 SET MINStart = CAST(MinStartNum as CHAR(2));
 END IF;

IF MinEndNum BETWEEN 0 AND 9 THEN
 SET MINEnd = '0'+CAST(MinEndNum as CHAR(1));
 ELSE
 SET MINEnd = CAST(MinEndNum as CHAR(2));
 END IF;

CASE DOWStartNum
           WHEN 1 THEN SET DOWStart='Monday';
           WHEN 2 THEN SET DOWStart='Tuesday';
           WHEN 3 THEN SET DOWStart='Wednesday';
           WHEN 4 THEN SET DOWStart='Thursday';
           WHEN 5 THEN SET DOWStart='Friday';
           WHEN 6 THEN SET DOWStart='Saturday';
           WHEN 7 THEN SET DOWStart='Sunday';
        END CASE;

CASE DOWEndNum
           WHEN 1 THEN SET DOWEnd='Monday';
           WHEN 2 THEN SET DOWEnd='Tuesday';
           WHEN 3 THEN SET DOWEnd='Wednesday';
           WHEN 4 THEN SET DOWEnd='Thursday';
           WHEN 5 THEN SET DOWEnd='Friday';
           WHEN 6 THEN SET DOWEnd='Saturday';
           WHEN 7 THEN SET DOWEnd='Sunday';
        END CASE;

CASE MONStartNum
           WHEN 1 THEN SET MONStart='Jan';
           WHEN 2 THEN SET MONStart='Feb';
           WHEN 3 THEN SET MONStart='Mar';
           WHEN 4 THEN SET MONStart='Apr';
           WHEN 5 THEN SET MONStart='May';
           WHEN 6 THEN SET MONStart='Jun';
           WHEN 7 THEN SET MONStart='Jul';
           WHEN 8 THEN SET MONStart='Aug';
           WHEN 9 THEN SET MONStart='Sep';
           WHEN 10 THEN SET MONStart='Oct';
           WHEN 11 THEN SET MONStart='Nov';
           WHEN 12 THEN SET MONStart='Dec';
        END CASE;

CASE MONEndNum
           WHEN 1 THEN SET MONEnd='Jan';
           WHEN 2 THEN SET MONEnd='Feb';
           WHEN 3 THEN SET MONStart='Mar';
           WHEN 4 THEN SET MONEnd='Apr';
           WHEN 5 THEN SET MONEnd='May';
           WHEN 6 THEN SET MONEnd='Jun';
           WHEN 7 THEN SET MONEnd='Jul';
           WHEN 8 THEN SET MONEnd='Aug';
           WHEN 9 THEN SET MONEnd='Sep';
           WHEN 10 THEN SET MONEnd='Oct';
           WHEN 11 THEN SET MONEnd='Nov';
           WHEN 12 THEN SET MONEnd='Dec';
        END CASE;

CASE DAYStartNum
           WHEN 1 THEN SET DAYStart='1st';
           WHEN 2 THEN SET DAYStart='2nd';
           WHEN 3 THEN SET DAYStart='3rd';
           WHEN 4 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 5 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 6 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 7 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 8 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 9 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 10 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 11 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 12 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 13 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 14 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 15 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 16 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 17 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 18 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 19 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 20 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 21 THEN SET DAYStart='21st';
           WHEN 22 THEN SET DAYStart='22nd';
           WHEN 23 THEN SET DAYStart='23rd';
           WHEN 24 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 25 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 26 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 27 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 28 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 29 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 30 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'th';
           WHEN 31 THEN SET DAYStart= CAST(DayStartNum as VARCHAR(2)) + 'st';
        END CASE;

CASE DAYEndNum
           WHEN 1 THEN SET DAYEnd='1st';
           WHEN 2 THEN SET DAYEnd='2nd';
           WHEN 3 THEN SET DAYEnd='3rd';
           WHEN 4 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 5 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 6 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 7 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 8 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 9 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 10 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 11 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 12 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 13 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 14 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 15 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 16 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 17 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 18 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 19 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 20 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 21 THEN SET DAYEnd='21st';
           WHEN 22 THEN SET DAYEnd='22nd';
           WHEN 23 THEN SET DAYEnd='23rd';
           WHEN 24 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 25 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 26 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 27 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 28 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 29 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 30 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'th';
           WHEN 31 THEN SET DAYEnd= CAST(DayEndNum as VARCHAR(2)) + 'st';
        END CASE;  

 IF aWithTimes THEN
    IF CAST(aStartDate as DATE) = CAST(aEndDate as DATE) THEN --the 2 days are the same
                    --on Monday 1st Jan 2011 from 10:00 to 12:00
        SET RESULT = 'on ' + DOWStart+' '+DAYStart+' '+MONStart+' '
                     +CAST(YEARStartNum as VARCHAR(4))+ ' from '
                     +CAST(HOURStartNum as VARCHAR(2))+':'+MINStart+' to '
                     +CAST(HOUREndNum as VARCHAR(2))+':'+MINEnd;
      ELSE
                   --'from Monday 1st Jan 2011 at 10:00 to Tuesday 2nd Jan 2011 at 10:00'
        SET RESULT = 'from '+DOWStart+' '+DAYStart+' '+MONStart+' '
                     +CAST(YEARStartNum as VARCHAR(4))+' at '+
                     +CAST(HOURStartNum as VARCHAR(2))+':'+MINStart+' to '
                     +DOWEnd+' '+DAYEnd+' '+MONEnd+' '
                     +CAST(YEAREndNum as VARCHAR(4))+' at '+
                     +CAST(HOUREndNum as VARCHAR(2))+':'+MINEnd;
      END IF;
  ELSE
    IF CAST(aStartDate as DATE) = CAST(aEndDate as DATE) THEN --the 2 days are the same
                    --on Monday 1st Jan 2011
        SET RESULT = 'on ' + DOWStart+' '+DAYStart+' '+MONStart+' '
                     +CAST(YEARStartNum as VARCHAR(4));
      ELSE
                   --'from Monday 1st Jan 2011 to Tuesday 2nd Jan 2011'
        SET RESULT = 'from '+DOWStart+' '+DAYStart+' '+MONStart+' '
                     +CAST(YEARStartNum as VARCHAR(4))+' to '
                     +DOWEnd+' '+DAYEnd+' '+MONEnd+' '
                     +CAST(YEAREndNum as VARCHAR(4));
      END IF;
 END IF;

RETURN RESULT;
END

Adam Brett,

> Here is a first (pretty basic) FUNCTION I would like to offer to post up on the examples site when it is ready.

Thank you for this function.

Just a remark: you can use arrays in your functions and thus avoid a lot
of writing

e.g.:

DECLARE DayCaptions VarChar ARRAY [7];
SET DayCaptions[1] = 'Monday';
SET DayCaptions[2] = 'Tuesday';
SET DayCaptions[3] = 'Wednesday';
SET DayCaptions[4] = 'Thursday';
SET DayCaptions[5] = 'Friday';
SET DayCaptions[6] = 'Saturday';
SET DayCaptions[7] = 'Sunday';

SET DOWStart = DayCaptions[DOWStartNum];

Regards Uli