Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Formated Date (Month) in SQL result |
Mon, Feb 18 2013 6:49 AM | Permanent Link |
Burkhard Schneider isyControl Software | 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 |
Mon, Feb 18 2013 7:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Burkhard
Look at CAST and FORMAT in the help in EDBmanager Roy Lambert [Team Elevate] |
Tue, Feb 19 2013 2:17 AM | Permanent Link |
Burkhard Schneider isyControl Software | 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). |
Tue, Feb 19 2013 4:27 AM | Permanent Link |
Uli Becker | 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 |
Tue, Feb 19 2013 5:56 AM | Permanent Link |
Burkhard Schneider isyControl Software | Uli,
Great!! I come from DBISAM and I'm just discovering the features of EBD. Burkhard |
Tue, Feb 19 2013 11:49 AM | Permanent Link |
Uli Becker | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |