Hi,

I need the month of a date in a format yyyymm in a SQL result an I need a Group by on this Value.

My Idea was this:

Select Nr, PROJ_NR, TEILLEIST,
      (IF (EXTRACT(MONTH FROM Datum)<10 then
       (Cast(EXTRACT(YEAR FROM Datum) as VARCHAR)+
        Cast(0 as VARCHAR)+
        Cast(EXTRACT(MONTH FROM Datum) as VARCHAR))
       Else
        (Cast(EXTRACT(YEAR FROM Datum) as VARCHAR)+
         Cast(EXTRACT(MONTH FROM Datum) as VARCHAR)))) as Month,
      sum(Anzahl) as Std
FROM Zeiten
Group By Nr, PROJ_NR, TEILLEIST, Month

It works, but isn't there something more elegant for the Month?

Regards,
Burkhard Schneider

Burkhard


Look at CAST and FORMAT in the help in EDBmanager

Roy Lambert [Team Elevate]

Roy,

Thank you, now i do this:

Select Nr, PROJ_NR, TEILLEIST,
      CAST (Datum as VARCHAR(6) DATE FORMAT 'yyyymmdd') as Month,
      sum(Anzahl) as Std
FROM Zeiten
Group By Nr, PROJ_NR, TEILLEIST, Month

Its a great function but it still needs a little workaround as the Format requires a day component whith I don't need. So I cut it off with Varchar(6).

Burkard,

you can use a function to simplify that, e.g.:

SCRIPT
BEGIN
EXECUTE IMMEDIATE 'CREATE FUNCTION "GetMonth" (IN "FDate" DATE)
RETURNS VARCHAR(6) COLLATE DEU_CI
BEGIN

RETURN LEFT(CAST(FDate as VARCHAR date Format ''yyyymmdd'') FOR 6);

END

VERSION 1.00';
END

And call it by:

Select Nr, PROJ_NR, TEILLEIST,
       GetMonth(Datum) as Month,
       sum(Anzahl) as Std
FROM Zeiten
Group By Nr, PROJ_NR, TEILLEIST, Month

Uli

Uli,

Great!!

I come from DBISAM and I'm just discovering  the features of EBD.

Burkhard

Burkhard,

> I come from DBISAM and I'm just discovering  the features of EBD.

It willl take some time to discover all the great features of EDB, but I
promise: you'll be very happy with it.

Uli