Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 28 total |
formatting time |
Fri, May 11 2007 10:06 AM | Permanent Link |
"Harry de Boer" | LS
having records with 'datum' (date), 'aanvang' and 'einde' (time), where I can have a couple of records of the same day I want to calculate the begin and endtime. select id_medewerker, datum, min(aanvang) as begin, max(einde) as einde, (max(einde) - min(aanvang)) as werktijd from registraties where id_medewerker = '20005' and datum between date'2004-07-01' and date'2005-04-01' group by id_medewerker, datum How do I format the output of werktijd like this '2 hours 55 minutes' in SQL Regards, Harry |
Fri, May 11 2007 2:57 PM | Permanent Link |
"Harry de Boer" | (cast(max(einde) - min(aanvang) as varchar(25))) as werktijd
comes pretty close. Regards, Harry "Harry de Boer" <harry@staaf.nl> schreef in bericht news:D3662F08-6B67-42C4-8E73-0F7A16D74C74@news.elevatesoft.com... > LS > > having records with 'datum' (date), 'aanvang' and 'einde' (time), where I > can have a couple of records of the same day I want to calculate the begin > and endtime. > > select id_medewerker, datum, > min(aanvang) as begin, max(einde) as einde, > (max(einde) - min(aanvang)) as werktijd > from registraties > where id_medewerker = '20005' > and datum between date'2004-07-01' and date'2005-04-01' > group by id_medewerker, datum > > How do I format the output of werktijd like this '2 hours 55 minutes' in SQL > > Regards, Harry > > |
Fri, May 11 2007 3:23 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Harry,
<< How do I format the output of werktijd like this '2 hours 55 minutes' in SQL >> Use this: select id_medewerker, datum, min(aanvang) as begin, max(einde) as einde, CAST((max(einde) - min(aanvang)) AS VARCHAR(20)) as werktijd from registraties where id_medewerker = '20005' and datum between date'2004-07-01' and date'2005-04-01' group by id_medewerker, datum to get 2:55 (example) as the result. If you want to get the full "x hours x minutes" text, then you'll need to do some further formatting with some SUBSTRING() calls or use a UDF like this: CREATE FUNCTION FormatHourMinuteInterval(IN Value INTERVAL HOUR TO MINUTE) RETURNS VARCHAR BEGIN DECLARE IntervalString VARCHAR DEFAULT ''; DECLARE SeparatorPos INTEGER DEFAULT 0; DECLARE Hours VARCHAR DEFAULT ''; DECLARE Minutes VARCHAR DEFAULT ''; SET IntervalString = CAST(Value AS VARCHAR); SET SeparatorPos = POS(':' IN IntervalString); SET Hours = LEFT(IntervalString FOR SeparatorPos-1); SET Minutes = SUBSTRING(IntervalString FROM SeparatorPos+1 FOR LENGTH(IntervalString)); RETURN Hours+' hours '+Minutes+' minutes'; END; -- Tim Young Elevate Software www.elevatesoft.com |
Fri, May 11 2007 3:25 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | And I almost forgot, you're going to want to use this instead for the
substraction: (max(einde) - min(aanvang)) HOUR TO MINUTE To make sure that the resulting interval is in hours/minutes, and not the default of HOUR TO MSECOND, or hours/mseconds. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, May 12 2007 5:07 AM | Permanent Link |
"Harry de Boer" | Tim,
> HOUR TO MINUTE to make sure that the resulting interval is in hours/minutes, and not the > default of HOUR TO MSECOND, or hours/mseconds. I tried it (see statement below), but it seems that the result (for werktijd1 and werktijd2) is the same. select id_medewerker, datum, min(aanvang) as begin, max(einde) as einde, ((max(einde) - min(aanvang)) hour to minute) as werktijd1, (max(einde) - min(aanvang)) as werktijd2 from registraties where id_medewerker = '20003' and datum between date'2004-07-01' and date'2005-04-01' group by id_medewerker, datum Regards, Harry "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht news:AA9082FB-9C68-4B58-A19D-7D773A335880@news.elevatesoft.com... > And I almost forgot, you're going to want to use this instead for the > substraction: > > (max(einde) - min(aanvang)) HOUR TO MINUTE > > To make sure that the resulting interval is in hours/minutes, and not the > default of HOUR TO MSECOND, or hours/mseconds. > > -- > Tim Young > Elevate Software > www.elevatesoft.com > > |
Sat, May 12 2007 5:11 AM | Permanent Link |
"Harry de Boer" | and....
CAST((max(einde) - min(aanvang)) AS VARCHAR(20)) as werktijd gives the same result as CAST((max(einde) - min(aanvang)) AS VARCHAR(25)) as werktijd Regards, Harry "Harry de Boer" <harry@staaf.nl> schreef in bericht news:D7AEEA1E-80CA-4B73-9917-FC307801DDFF@news.elevatesoft.com... > Tim, > > > HOUR TO MINUTE to make sure that the resulting interval is in > hours/minutes, and not the > > default of HOUR TO MSECOND, or hours/mseconds. > > I tried it (see statement below), but it seems that the result (for > werktijd1 and werktijd2) is the same. > > select id_medewerker, datum, > min(aanvang) as begin, max(einde) as einde, > ((max(einde) - min(aanvang)) hour to minute) as werktijd1, > (max(einde) - min(aanvang)) as werktijd2 > from registraties > where id_medewerker = '20003' > and datum between date'2004-07-01' and date'2005-04-01' > group by id_medewerker, datum > > Regards, Harry > > > "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht > news:AA9082FB-9C68-4B58-A19D-7D773A335880@news.elevatesoft.com... > > And I almost forgot, you're going to want to use this instead for the > > substraction: > > > > (max(einde) - min(aanvang)) HOUR TO MINUTE > > > > To make sure that the resulting interval is in hours/minutes, and not the > > default of HOUR TO MSECOND, or hours/mseconds. > > > > -- > > Tim Young > > Elevate Software > > www.elevatesoft.com > > > > > > |
Mon, May 14 2007 5:33 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Harry,
<< I tried it (see statement below), but it seems that the result (for werktijd1 and werktijd2) is the same. >> It will give you the same value without the CAST to a string representation. What I was referring to was using the UDF that I posted, which requires a HOUR TO MINUTE interval as the parameter. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, May 14 2007 5:35 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Harry,
<< and.... CAST((max(einde) - min(aanvang)) AS VARCHAR(20)) as werktijd gives the same result as CAST((max(einde) - min(aanvang)) AS VARCHAR(25)) as werktijd >> Correct, the length of the VARCHAR won't matter in this case. What you want is this: CAST((max(einde) - min(aanvang)) HOUR TO MINUTE AS VARCHAR(20)) as werktijd -- Tim Young Elevate Software www.elevatesoft.com |
Tue, May 15 2007 12:09 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Harry
Somewhat off thread but whereabouts in the manual is the need to stick DATE in front of the date constant (eg DATE 2005-04-01')? And do you know of any more like this eg Timestamp? Roy Lambert |
Tue, May 15 2007 5:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Somewhat off thread but whereabouts in the manual is the need to stick DATE in front of the date constant (eg DATE 2005-04-01')? And do you know of any more like this eg Timestamp? >> All of the date/time constants require it: http://www.elevatesoft.com/edb1sql_date_time_types.htm -- Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |