1 / 19

Dates and Functions

Dates and Functions. Noreen Redden Information Builders, Inc. Types of Dates Traditional Dates. Traditional Dates A6YMD , I8YYMD , A6MYY , I4MTYY Component Declaration for Editing only No Validation Default in Dialogue Manager Date Components I2Y , I4YY, Y or YY are just integers

jam
Download Presentation

Dates and Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Dates and Functions Noreen Redden Information Builders, Inc

  2. Types of DatesTraditional Dates • Traditional Dates • A6YMD , I8YYMD , A6MYY , I4MTYY • Component Declaration for Editing only • No Validation • Default in Dialogue Manager • Date Components • I2Y , I4YY, Y or YY are just integers • MT is a 2 digit integer with value from 1 to 12 • D is a 2 digit integer with value from 1 to 31.

  3. Functions and Traditional Dates • Select Components -SET &MONTH = EDIT(&YYMD , ’$$$$99’); • Change Order of Components -SET &DTMDY = EDIT(&YYMD , '$$$$99$$') | - EDIT(&YYMD, '$$$$$$99') | - EDIT(&YYMD , '$$99') ; -TYPE DTMDY IS &DTMDY -SET &DTMDY2 = CHGDAT('YYMD' , 'MDYY' ,&YYMD , - 'A8MDYY'); -SET &DTMDY3 = &MDYY ;

  4. Types of Dates • “Smart Dates” • MYY, YYMD, MTrDYY, • Displacement from Base Date • FOCUS displacement from 12/31/1900 (day component) or 01/1901 (month/year only) • Also supports W (day of week) Q (Quarter) • DEFCENT and YRTHRESH • Convert from Traditional Date to/from Smart Date • DEFINE/COMPUTE or ACTUAL to USAGE datefield/dateformat = traddate; traddate/A8YYMD = datefield; • Use DATECVT

  5. Functions and Smart Dates • Select Components, Change order of Components, Convert from Traditional to/from Smart Date DATECVT(date, 'infmt', 'outfmt'[, outfield]) DEFINE FILE EMPLOYEE HIRE_DATE/YYM DEFCENT 19 YRTHRESH 50 = DATECVT(HIRE_DATE,’I6YMD’,’YYM’); END -SET &DT2 = DATECVT (100101,'I6YMD','YYM'); -SET &DT3 = DATECVT (&DT2 , 'YYM', 'I8YYMD'); • Subtract two Dates DATEDIF (from_date, to_date, component_code) DEFINE FILE SUPPORT DAYS/I5 = END_DT – START_DT; WORK_DAYS/I5 = DATEDIF (END_DT,START_DT,’WD’);

  6. Functions and Smart Dates • Add/Subtract Days/Months DATEADD(date, 'unit', #units[, outfield]) Function adds a unit to or subtracts a unit from a date format DEFINE FILE EMPLOYEE HIRE_SMT/YYMD DEFCENT 19 YRTHRESH 50 = DATECVT(HIRE_DATE,'I6YMD','YYMD'); HIRE_WK/YYMD = HIRE_SMT + 7; MONANN/YYMD = DATEADD(HIRE_SMT ,'M' , 6 ); END • Find Last Day of a Month DATEMOV(YYMDdate, 'move-point') -SET &DT2 =DATECVT((DATEMOV( - (DATECVT (&YYMD ,'I8YYMD', • 'YYMD' )),'EOM')),'YYMD','I8YYMD');

  7. Date Functions Arguments for Use With Date Functions Argument ValueDefinition Y year M month D Day of month WD Working Day BD Excludes Holidays BOM/EOM Beginning/End of Month BOM/EOQ Beginning/End of Quarter BOY/EOY Beginning/End of Year BOW/EOW Beginning/End of Week NWD/NBD Next Business/Week Day PWD/PBD Prior Business/Week Day WD+/BD+ Move forward to get to week/business day if necessary WD-/BD- Move back to get to week/business day if necessary

  8. Types of Dates • Date-Time (time stamps) • HMDYYS – displays 02/27/2010 11:00:05 • HYYMDs – displays 2010/02/27 11:00:05.444 • HYYMDSA – displays 2010/02/27 11:00:05AM • Convert to Time Stamps HDTTM Convert from date field to date-time HINPUT Convert from alpha to date-time • Convert from Time Stamps HCNVRT Convert to alpha format HDATE Convert date portion to YYMD

  9. Functions and Time Stamps • Select Components HPART – passed to relational engine in 7.7 HPART (value, component, outfield) IHOUR/I2 = HPART(JOB_TIME,'HOUR', IHOUR); • Subtract 2 timestamps HDIFF (end_timestamp, start_timestamp, component, outfield) DEFINE FILE JOBS DIFSEC/I9 = HDIF(ENDTM, STTM,'SECOND', 'D9.0'); END

  10. Functions and Time Stamps • Incrementing Date-Time Fields HADD (timestamp, component, increment, length, outfield) DEFINE FILE JOBS NXTDUE/HYYMDS = HADD(ST_TIME), 'MONTH', 6, 8, 'HYYMDS'); END • Storing the Current Date/Time as a Time Stamp HGETC(length, outfield)

  11. Date-Time Functions Arguments for Use With Date and Time Functions Component NameValid Values year 0001‑9999 quarter 1‑4 month 1‑12 day‑of‑year 1‑366 day or day‑of‑month 1‑31 week 1‑53 weekday 1‑7 (Sunday‑Saturday) hour 0‑23 minute 0‑59 second 0‑59 millisecond 0‑999 microsecond 0‑999999

  12. Functions • Functions may be used against any field in any file, so long as proper arguments are supplied. Functions may be use anywhere an expression is allowed. • IBI-supplied functions may be augmented by user-written subroutines, called by FOCUS and coded in a 3GL language. • IBI-supplied functions may be augmented by user-written functions written in FOCUS language DEFINE FUNCTION name (argument1/format1,..., argumentn/formatn)[tempvariablea/formata = expressiona;] ...[tempvariablex/formatx = expressionx;] name/format = [result_expression];END

  13. FOCUS Functions WORKWEEK DEFINE FUNCTION WORKWEEK (INPTDT/I8) DT1/YYMD = DATECVT(INPTDT , 'I8YYMD' , 'YYMD'); DTTIME/HYYMDIA = HDTTM(DT1, 8, 'HYYMDIA'); WORKWEEK/I2 = HPART(DTTIME, 'WEEK', 'I2'); END -SET &WORKWEEK = WORKWEEK(&YYMD ); -RUN DEFINE FILE EMPLOYEE INPTDT/I8YYMD DEFCENT 19 YRTHRESH 50 = DATECVT(HIRE_DATE, 'I6YMD', 'I8YYMD'); WORKWEEK/I2 = WORKWEEK(INPTDT); END

  14. Work Week PAGE 1 THE CURRENT DATE February 22, 2010 IS IN WORK WEEK 8 EMP_ID LAST_NAME HIRE_DATE WORKWEEK ------ --------- --------- -------- 071382660 STEVENS 80/06/02 22 112847612 SMITH 81/07/01 26 117593129 JONES 82/05/01 18 119265415 SMITH 82/01/04 1 119329144 BANNING 82/08/01 31 123764317 IRVING 82/01/04 1 126724188 ROMANS 82/07/01 26 219984371 MCCOY 81/07/01 26 326179357 BLACKWOOD 82/04/01 13 451123478 MCKNIGHT 82/02/02 5 543729165 GREENSPAN 82/04/01 13 818692173 CROSS 81/11/02 44

  15. Handling Times We Need A Report That Reads Times In Seconds, Adds Them And Converts To Hours : Minutes : Seconds AGENT1 AGENT_TALK AGENT_TALK1 TTIME TTIME1 ALLEN 200 300 00:03:20 00:05:00 BILL 300 400 00:05:00 00:06:40 CARL 620 520 00:10:20 00:08:40 DAN 400 901 00:06:40 00:15:01 --------------------------------------------------------- TOTAL 1520 2121 00:25:20 00:35:21

  16. Handling Times FILEDEF AGNT DISK C:\IBI\APPS\DATES\AGNT.FTM FILEDEF FSEQ DISK C:\IBI\APPS\DATES\FSEQ.MAS DEFINE FUNCTION HRMISC (SEC/I10) -* CALCULATE HOURS HR/I2 = SEC/3600; HR_R/I2 = SEC -(HR* 3600) ; -* CALCULATE MIN MI/I2 = (HR_R / 60); -* CALCULATE SECONDS SC_D/I2 = SEC -((MI * 60) + HR); HRMISC/A8 = EDIT(HR) || ':' || EDIT(MI) || ':' || EDIT(SC_D) ; END DEFINE FILE AGNT TTIME/A8 = HRMISC(AGENT_TALK); TTIME1/A8 = HRMISC(AGENT_TALK1); END

  17. Handling Times FILEDEF AGNT DISK C:\IBI\APPS\DATES\AGNT.FTM FILEDEF FSEQ DISK C:\IBI\APPS\DATES\FSEQ.MAS DEFINE FUNCTION HRMISC (SEC/I10) -* CALCULATE HOURS HR/I2 = SEC/3600; HR_R/I2 = SEC -(HR* 3600) ; -* CALCULATE MIN MI/I2 = (HR_R / 60); -* CALCULATE SECONDS SC_D/I2 = SEC -((MI * 60) + HR); HRMISC/A8 = EDIT(HR) || ':' || EDIT(MI) || ':' || EDIT(SC_D) ; END DEFINE FILE AGNT TTIME/A8 = HRMISC(AGENT_TALK); TTIME1/A8 = HRMISC(AGENT_TALK1); END

  18. Handling Times TABLE FILE AGNT PRINT AGENT_TALK NOPRINT AGENT_TALK1 NOPRINT TTIME TTIME1 COMPUTE AVER1/I9 = (AGENT_TALK + AGENT_TALK1) / 2 + .5; NOPRINT COMPUTE AVERT/A8 = HRMISC(AVER1); AS 'AVERAGE' COMPUTE CNTAGENT/I5 = 1; NOPRINT BY AGENT ON TABLE RECAP TOTTIME/A8 = HRMISC(AGENT_TALK); TOTTIME1/A8 = HRMISC(AGENT_TALK1); TOTAVER/A8 = HRMISC((AGENT_TALK + AGENT_TALK1) /2); AVETIME/A8 = HRMISC(AGENT_TALK / CNTAGENT); AVETIME1/A8 = HRMISC(AGENT_TALK1 / CNTAGENT); AVEATIME/A8 = HRMISC((AGENT_TALK + AGENT_TALK1)/CNTAGENT/2); ON TABLE SUBFOOT "</1 TOTAL <TOTTIME <TOTTIME1 <TOTAVER " "AVERAGE <AVETIME <AVETIME1 <AVEATIME" END

  19. Handling Times AGENT TTIME TTIME1 AVERAGE ----- ----- ------ ------- ALLEN 00:03:20 00:05:00 00:04:10 BILL 00:05:00 00:06:40 00:05:50 CARL 00:10:20 00:08:40 00:09:30 DAN 00:06:40 00:15:01 00:10:51 TOTAL 00:25:20 00:35:21 00:30:20 AVERAGE 00:06:20 00:08:50 00:07:35

More Related