1 / 41

What's New With Dates

What's New With Dates. Walter Brengel Information Builders New Jersey User Group, 2009. What's New With Dates OVERVIEW. Review Legacy Vs Smart Dates Dialogue Manager and Dates DATETIME Functions What's New Functions Displaying Dates Date Manipulation.

chico
Télécharger la présentation

What's New With Dates

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. What's New With Dates Walter Brengel Information Builders New Jersey User Group, 2009

  2. What's New With Dates OVERVIEW • Review • Legacy Vs Smart Dates • Dialogue Manager and Dates • DATETIME • Functions • What's New • Functions • Displaying Dates • Date Manipulation

  3. What's New With Dates Review – Legacy Dates Vs. Smart Dates Legacy Dates Dates Physically Stored As A Recognizable Date Determined By USAGE: A8YYMD ('20090317') A6YMD ('090317') I8YYMD (20090317) I6YMD (090317)

  4. What's New With Dates Review – Legacy Dates Vs. Smart Dates Legacy Dates Considerations CANNOT Do Date Arithmetic Can Be Used To Specify Date Range Only If The Format is A8YYMD or I8YYMD BEGDATE/I8YYMD=20090315; ENDDATE/I8YYMD=20090415; DIFFDAYS/I5=ENDDATE – BEGDATE; Result of DIFFDAYS: 100 DIFFDAYS=ENDDATE – BEGDATE; 20090415 - 20090315 OPENDATE has USAGE of A8MDYY and VALUE of '05312008' WHERE OPENDATE GE '01012009' Will OPENDATE of 05312008 Show In The Output? YES – A String Starting With 05 Is Greater Than a String Starting with 01

  5. What's New With Dates Review – Legacy Dates Vs. Smart Dates Smart Dates Dates Physically Stored As The OFFSET of Days From 1900/12/31 Determined By USAGE: YYMD - 20090317 YYMD (39523) YMD (39523) What Is Stored If The FORMAT is YMD and the VALUE is 090317? How Does FOCUS Know If The Value Is 20090317 or 19090317? DEFCENT and YRTHRESH: DEFCENT = 19 and YRTHRESH <= year use DEFCENT - 2998 DEFCENT = 19 and YRTHRESH > year use DEFCENT + 1 - 39523

  6. What's New With Dates Review – Legacy Dates Vs. Smart Dates Converting Dates With DEFINE or COMPUTE Legacy to Smart: NEWDATE/MDYY=LEGACYDATE; Smart to Legacy: NEWDATE/A8YMD=SMARTDATE; Why Convert? To Perform Date Arithmetic To Screen Data Not in YYMD format

  7. What's New With Dates Review – Dialogue Manager There Are No Date Formats In Dialogue Manager A Date Must Be Converted To A Smart Date Using The DATECVT Subroutine, Perform Calculation. If Necessary, Convert Back To A Legacy Date.

  8. What's New With Dates Review – Dialog Manager Example: Calculate the number of days from now until Halloween. -SET &THISYEAR = EDIT(&YYMD,'9999'); -SET &THISHALL = &THISYEAR | '1031'; -* CONVERT DATES TO SMARTDATES -SET &STODAY = DATECVT(&YYMD,'I8YYMD','YYMD'); -SET &SHALLOW = DATECVT(&THISHALL,'I8YYMD','YYMD'); -* SUBTRACT THE 2 SMART DATES TO GET DIFFERENCE -SET &DAYSTOHALL = &SHALLOW - &STODAY; -TYPE DAYS TO HALLOWEEN: &DAYSTOHALL Output of &ECHO=ALL: • -SET &THISYEAR = EDIT(20090419,'9999'); • -SET &THISHALL = 2009 | '1031'; • -* CONVERT DATES TO SMARTDATES • -SET &STODAY = DATECVT(20090419,'I8YYMD','YYMD'); • -SET &SHALLOW = DATECVT(20091031,'I8YYMD','YYMD'); • -* SUBTRACT THE 2 SMART DATES TO GET DIFFERENCE • -SET &DAYSTOHALL = 39751 - 39556; • -TYPE DAYS TO HALLOWEEN: 195 • DAYS TO HALLOWEEN: 195

  9. What's New With Dates Review – DATETIME formats Dependent On The Adapter FOCUS FORMAT=H17 can store: YYYYMMDDHHMMSSsss (millisecond) FORMAT=H20 can store: YYYYMMDDHHMMSSsssmmm (microsecond)

  10. What's New With Dates Review – DATETIME formats Oracle - TIMESTAMP USAGE=HYYMDS, ACTUAL=HYYMDS stores: YYYYMMDDHHMMSS 2008/01/01 13:59:23 USAGE=HYYMDs, ACTUAL=HYYMDs stores: YYYYMMDDHHMMSSsss 2008/01/01 13:59:23.218 USAGE=HYYMDm, ACTUAL=HYYMDm stores: YYYYMMDDHHMMSSsssmmm 2008/01/01 13:59:23.218431

  11. What's New With Dates Review – DATETIME formats MS SQL Server DATETIME USAGE=HYYMDs, ACTUAL=HYYMDs stores: YYYYMMDDHHMMSSsss (milliseconds) range: 1/1/1753 to 12/31/9999 Why 1753?

  12. What's New With Dates Review – DATETIME formats DB2 TIMESTAMP USAGE=HYYMDm, ACTUAL=HYYMDm stores: YYYYMMDDHHMMSSsssmmm (microseconds)

  13. What's New With Dates Review – Formats * SET YRTHRESH=50

  14. What's New With Dates Review – Functions DATECVT converts the format of a date in an application without requiring an intermediate calculation DATEADD function adds a unit to or subtracts a unit from a date format HDTTM function converts a date value to a date‑time field HPART function extracts a specified component from a date‑time value and returns it in numeric format HINPUT function converts an alphanumeric string to a date‑time value. HDIFF function calculates the number of units between two date-time values

  15. What's New With Dates Review – Functions DATECVT(date, 'infmt', 'outfmt'[, outfield]) Converts the format of a date in an application without requiring an intermediate calculation DATECVT(HIRE_DATE, 'I6YMD', 'YYMD') where: date - Is the date to be converted. If you supply an invalid date, DATECVT returns zero. When the conversion is performed, a legacy date obeys any DEFCENT and YRTHRESH parameter settings supplied for that field. infmt - Alphanumeric Is the format of the date enclosed in single quotation marks. It is one of the following: A non-legacy date format (for example, YYMD, YQ, M, DMY, JUL). A legacy date format (for example, I6YMD or A8MDYY). A non-date format (such as I8 or A6). A non-date format in infmt functions as an offset from the base date of a YYMD field (12/31/1900). outfmt - Alphanumeric Is the output format enclosed in single quotation marks. It is one of the following: A non-legacy date format (for example, YYMD, YQ, M, DMY, JUL). A legacy date format (for example, I6YMD or A8MDYY). A non-date format (such as I8 or A6). A non-date format in infmt functions as an offset from the base date of a YYMD field (12/31/1900). Outfield - Alphanumeric Is the field that contains the result. This value is required only for Maintain.

  16. What's New With Dates Review – Functions DATEADD(date, 'unit',#units[, outfield]) Function adds a unit to or subtracts a unit from a date format DATEADD(NEW_DATE, 'WD', 3) where: date - Is any day-based non-legacy date, for example, YYMD, MDY, or JUL. unit – Alphanumeric Is one of the following enclosed in single quotation marks: Y indicates a year unit. M indicates a month unit. D indicates a day unit. WD indicates a weekday unit. BD indicates a business day unit. #units – Integer Is the number of date units added to or subtracted from date. If this number is not a whole unit, it is rounded down to the next largest integer. outfield – Alphanumeric Is the field that contains the result. This value is required only for Maintain.

  17. What's New With Dates Review – Functions HDTTM(date, length, outfield) Function converts a date value to a date‑time field HDTTM(TRANSDATE_DATE, 8, 'HYYMDIA') where: date - Is the date value to be converted, the name of a date field that contains the value, or an expression that returns the value. length - Is the length of the returned date‑time value. Valid values are: 8 indicates a time value that includes milliseconds. 10 indicates a time value that includes microseconds. Outfield - Alphanumeric Is the field that contains the result, or the format of the output value enclosed in single quotation marks. The format must be in date-time format (data type H). In FOCUS, you must specify the format. In Maintain, you must specify the name of the field.

  18. What's New With Dates Review – Functions HPART(value, 'component', outfield) Function extracts a specified component from a date‑time value and returns it in numeric format HPART(TRANSDATE, 'DAY', 'I2') where: value - Is a date‑time value, the name of a date‑time field that contains the value, or an expression that returns the value. component - Is the name of the component to be retrieved enclosed in single quotation marks. See Arguments for Use With Date and Time Functions for a list of valid components. Outfield - Numeric Is the field that contains the result, or the integer format of the output value enclosed in single quotation marks. In FOCUS, you must specify the format. In Maintain, you must specify the name of the field.

  19. What's New With Dates Review – Functions HINPUT(inputlength, 'inputstring',length, outfield) Function converts an alphanumeric string to a date‑time value HINPUT(14, ALPHA_DATE_TIME, 8, 'HYYMDS') where: inputlength Is the length of the alphanumeric string to be converted. You can supply the actual value, the name of a numeric field that contains the value, or an expression that returns the value. inputstring Is the alphanumeric string to be converted enclosed in single quotation marks, the name of an alphanumeric field that contains the string, or an expression that returns the string. The string can consist of any valid date‑time input value as described in Describing Data. length Is the length of the returned date‑time value. Valid values are: 8 indicates a time value that includes milliseconds. 10 indicates a time value that includes microseconds. outfield - Alphanumeric Is the field that contains the result, or the format of the output value enclosed in single quotation marks. The format must be in date-time format (data type H). In FOCUS, you must specify the format. In Maintain, you must specify the name of the field.

  20. What's New With Dates Review – Functions HDIFF(value1, value2, 'component', outfield) function calculates the number of units between two date-time values HDIFF(ADD_MONTH, TRANSDATE, 'DAY', 'D12.2') where: value1 - Is the end date‑time value, the name of a date‑time field that contains the value, or an expression that returns the value. value2 - Is the start date‑time value, the name of a date‑time field that contains the value, or an expression that returns the value. component - Is the name of the component to be used in the calculation enclosed in single quotation marks. If the component is a week, the WEEKFIRST parameter setting is used in the calculation. outfield - Alphanumeric Is the field that contains the result, or the format of the output value enclosed in single quotation marks. The format must be floating-point double-precision. In FOCUS, you must specify the format. In Maintain, you must specify the name of the field.

  21. What's New With Dates Review – Functions Arguments for Use With Date and Time Functions The following component names and values are supported as arguments for the date‑time functions that require them: Component NameValid Values year 0001‑9999 quarter 1‑4 month 1‑12 day‑of‑year 1‑366 day or day‑of‑month 1‑31 (The two names for the component are equivalent.) week 1‑53 weekday 1‑7 (Sunday‑Saturday) hour 0‑23 minute 0‑59 second 0‑59 millisecond 0‑999 microsecond 0‑999999

  22. What's New With Dates TIME Functions HHMMSS – retrieves the current time DATETIME Functions HADD – increments a date-time value HDATE – converts the date portion of a date-time value to a date format HGETC – retrieves the current date and time in a date-time field HNAME – retrieves a date-time component in alphanumeric format HTIME – extracts the time portion of a date-time value to a number HYYWD – returns the year and week number from a date-time value • DATE Functions • DATEDIF –finding the difference between two dates • TODAY –returns the current date • DATEMOV –moving a date to a significant point • DATETRAN – format dates in international formats

  23. What's New With Dates TIME Functions HHMMSS(outfield) Retrieves the current time HHMMSS('A8') CURTIME/A8=HHMMSS('A8'); Output: 09.20.13 where: outfield – Alphanumeric, at least 8 characters Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks. In Dialogue Manager you must specify the format. In Maintain, you must specify the name of the field.

  24. What's New With Dates DATETIME Functions HADD(infield, 'component',increment, length, outfield) Increments A Date-Time Value HADD(TRANSDATE,'MONTH',1,8,'HYYMDS') where: infield – Date-Time Is the date-time value to be incremented, the name of a date-time field that contains the value or an expression that returns the value. component – Alphanumeric Is the name of the component to be incremented enclosed in single quotation marks. See Arguments for Use With Date and Time Functions for a list of valid components. increment – Integer Is the number of units by which to increment the component, the name of a numeric field that contains the value, or an expression that returns the value. length – Integer Is the length of the returned date-time value. Valid values are: 8 indicates a time value that includes milliseconds 10 indicates a time value that includes microseconds outfield – Date-Time Is the field that contains the result, or the format of the output value enclosed in single quotation marks. It must be a DATE-TIME format. In Maintain, the name of the field is required.

  25. What's New With Dates DATETIME Functions HADD example Input date of STDATE is 2008/09/23 15:30:00.000 STDATE plus 2 months is derived using: STDATEP2M/HYYMDs=HADD(STDATE,'MONTH',2,10,'HYYMDs'); The result is: 2008/11/23 15:30:00.000

  26. What's New With Dates DATETIME Functions HDATE(value, '(YYMD)', outfield) Converts the date portion of a Date-Time field to a Date value HDATE(DATETIMEFLD,outfield) where: value – Date-Time Is the date-time value to be converted, the name of a date-time field that contains the value, or an expression that returns the value. outfield – YYMD For Maintain, this is the name of the output field otherwise it will be YYMD. HDATE example: Input date of STDATE is 2008/09/23 15:30:00.000 DATEONLY/YYMD=HDATE(STDATE,'YYMD'); The result is: 2008/09/23

  27. What's New With Dates DATETIME Functions HGETC(length, outfield) Stores current date-time into a date-time field. HGETC(8, 'HYYMDs') where: length – Integer Is the length of the returned date-time value. Valid values are: 8 – indicates a time value that includes milliseconds 10 – indicates a time value that includes microseconds outfield – date-time Is the field that contains the result, or format or the output value enclosed in single quotation marks. The format must be in date-time format (data type H). In Maintain, you must specify the name of the field. HGETC example: CURRDATETIME/HYYMDs=HGETC(10,'HYYMDs'); The result is: 2009/04/21 09:32:35.215

  28. What's New With Dates DATETIME Functions HTIME(length, value, outfield) Converts the time portion of a datetime value to a value HTIME(8,TRANSDATE, 'D12.2') where: length – Integer Is the length of the input date‑time value. Valid values are: 8 indicates a time value that includes milliseconds. 10 indicates a time value that includes microseconds. value – Date-Time Is the date-time value from which to convert the time, the name of a date-time field that contains the value, or an expression that returns the value. outfield – Floating Point or Double Precision number Is the field that contains the result, or the format of the output value enclosed in single quotation marks. The format must be floating point or double precision. In Maintain, you must specify the name of the field.

  29. What's New With Dates DATETIME Functions HNAME(value, 'component', outfield) Function extracts a specified component from a date‑time value and returns it in alphanumeric format HNAME(TRANSDATE, 'DAY', 'A20') where: value - Is a date‑time value, the name of a date‑time field that contains the value, or an expression that returns the value. component - Is the name of the component to be retrieved enclosed in single quotation marks. See Arguments for Use With Date and Time Functions for a list of valid components. Outfield – Alphanumeric, at least A2 Is the field that contains the result, or the alphanumeric format of the output value enclosed in single quotation marks. In Maintain, you must specify the name of the field. The function converts all the other components to strings of digits only. The year is always four digits and the hour assumes the 24-hour system.

  30. What's New With Dates DATETIME Functions HNAME example Input date of STDATE is 2008/09/23 15:30:00.000 Extracting Month from date-time: EXTMONTH/A20=HNAME(STDATE, 'MONTH', 'A20'); The result is: SEP

  31. What's New With Dates DATETIME Functions HYYWD(dtvalue, outfield) Returns the year and week number from a date-time value HYYWD(OPEN_DT, 'A10') 2008/09/23 15:30:00.000 where: Output: 2008-W38-4 dtvalue – date-time Is the date-time value to be edited, the name of a date-time field that contains the value, or, or an expression that returns the value. outfield - Alphanumeric Is the field that contains the result, or the format of the output value enclosed in single quotation marks. The field must be at least 10 characters long. The output is in the following format: yyyy-Www-d where: yyyy – is the four digit year ww – is the two digit week number (01 – 53) d – is the single digit day of the week (1 to 7). The d value is relative to the current WEEKFIRST setting. If WEEKFIRST is 2 or ISO2 (Monday), then Monday is represented in the output as 1, Tuesday as 2. Using the EDIT function, you can extract the individual subfields from this output.

  32. What's New With Dates DATETIME Functions • To determine a week number, they can use ISO 8601 standard week numbering, which defines the first week of the year as the first week in January with four or more days. Any preceding days in January belong to week 52 or 53 of the preceding year. The ISO standard also establishes Monday as the first day of the week

  33. What's New With Dates DATE Functions DATEDIF(from_date, to_date, 'unit', outfield) Calculates the number of units between two date-time values DATEDIF(BEGDATE,ENDDATE, 'M',, 'D12.2') where: from_date – Date Is the start date from which to calculate the difference. to_date – Date Is the end date from which to calculate the difference. unit – Alphanumeric Is one of the following enclosed in single quotation marks: Y for year WD for weekday M for month BD for business day D for day outfield - Numeric Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks. In Maintain, you must specify the name of the field.

  34. What's New With Dates DATE Functions TODAY(outfield) Returns the current date TODAY('A8') where: outfield – Alphanumeric, at least A8 Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks. The following apply: If DATEFNS=ON and the format is A8 or A9, TODAY returns the 2 digit year If DATEFNS=ON and the format is A10 or greater, TODAY returns the 4 digit year If DATEFNS=OFF, TODAY returns the 2 digit year, regardless of the format of outfield In Dialogue Manager, you must specify the format. In Maintain, you must specify the name of the field.

  35. What's New With Dates DATE Functions DATEMOV(date, 'move-point') Moves a date to a significant point DATEMOV(OPENDATE,'BOM') where: from_date – Date Is the date to be moved. move-point – Alphanumeric Is the significant point the date is moved to enclosed in single quotation marks. An invalid point results in a return code of zero. Valid values are: EOM – end of month NWD – next weekday BOM – beginning of month NBD – next business day EOQ – end of quarter PWD – prior weekday BOQ – beginning of quarter PBD – prior business day EOY – end of year WD- – weekday or earlier BOY – beginning of year BD- – business day or earlier EOW – end of week WD+ - weekday or later BOW – beginning of week BD+ - business day or later Business day calculations are affected by BUSDAYS and HDAY.

  36. What's New With Dates DATE Functions DATETRAN(indate, '(intype)', '(formatopts)', 'lang', outlen, outfield) Formats dates in International formats DATETRAN(DATEFIELD, '(MDYY)', '(wrctrdo)', 'EN', 150, FMTDATE) will print: Tuesday, March 17th 2009 where: indate – Date Is the date to be formatted. intype – Alphanumeric Is the format that the date will be displayed in. formatopts – Alphanumeric Identifies the formatting to take place. Suppression formats m – Zero suppresses months (display numeric months before October as 1 to 9 rather than 01 through 09). d – Zero suppresses days. dp – same as d with a period after the day do – Zero suppresses days, for English will print the ordinal suffix after the number.

  37. What's New With Dates DATE Functions formatopts – continued Month and Day Name translations: T – displays month abbreviated, all uppercase, no punctuation. (JAN) TR – display full month, all uppercase. (JANUARY) Tp – same as T with a period after the abbreviated month. (JAN.) t – displays abbreviated month, no punctuation, first character is capital based on language setting. (Jan) tr – displays full month, all lowercase, first character uppercase based on language setting. (January) tp – same as t with period after abbreviated month. (Jan.) W – abbreviated day of the week name before the displayed date, all upper case, no punctuation. (TUE) WR – full day of week before the date, all uppercase. (TUESDAY) Wp – same as W with period after abbreviated weekday. (TUE.) w – abbreviated day of week, lowercase, first character capital based on language setting. (Tue) wr – full day of week, all lowercase, first character capital based on language setting. (Tuesday) wp – same as w with period after abbreviated weekday. (Tue.)

  38. What's New With Dates DATE Functions formatopts – continued X – displays abbreviated day of the week at the end of the displayed date, all uppercase, no punctuation. (WED) XR – display full day of the week after date, all uppercase. (WEDNESDAY) Xp – same as X with a period after the abbreviated weekday. (WED.) x – displays abbreviated weekday after date, no punctuation, first character is capital based on language setting. (Wed) xr – displays full weekday after date, all lowercase, first character uppercase based on language setting. (Wednesday) xp – same as x with period after abbreviated weekday. (Wed.) Delimiter options: B – use a blank as delimiter. Default for month or day of week. . – use a period as delimiter - – use a minus sign as delimiter. This is the default when conditions for a blank are not satisfied. / – use a slash as the delimiter | – omits delimiters K – Use comma after month name, comma space after the day name

  39. What's New With Dates DATE Functions formatopts – continued c – places comma after the month name, or comma blank after day name. e – displays the Spanish or Portugues word de or DE between the day and month and between the month and year. D –Inserts comma after the day name and before the general delimiter Y – Inserts a comma after the year and before the general delimiter lang – Alphanumeric the two character standard ISO code for the language into which the date should be translated, enclosed in single quotation marks. Valid language codes are: AR Arabic EL Greek PO Polish CS Czech IW Hebrew PT Portuguese DA Danish IT Italian RU Russian DE German JA Japanese SV Swedish EN English KO Korean TH Thai ES Spanish LT Lithuanian TW Chinese (Traditional) FI Finnish NL Dutch ZH Chinese (Simplified) FR French NO Norwegian

  40. What's New With Dates DATE Functions outlen – Numeric The length of the output field in bytes. If the length is insufficient, an all blank result is returned. If the length is greater than required, the field is padded with blanks on the right. output – Alphanumeric Is the name of the field that contains the translated date, or it format enclosed in single quotation marks.

  41. What's New With Dates Questions? Thank you for coming!

More Related