[Tutorial] Date-Time Conversion in StarBasic

Home made tutorials, by users, for users
Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Locked
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Tutorial] Date-Time Conversion in StarBasic

Post by Villeroy »

Dates, times and combined date-times (time stamps) are complex data types. A date consists of 3 numbers year, month and day. A time consists of 4 numbers hours, minutes, seconds and fractions of seconds. A time stamp comprises all 7 numbers. Additional information such as quarter, weekday, week number, month name and weekday name can be derived by means of spreadheet functions, SQL functions, Basic functions or built-in functions of other macro languages. This tutoral is about a helper function ConvertDateTime I wrote in StarBasic for StarBasic programmers to facilitate all conversions between all possible types of dates, times and time stamps. You can integrate the code into your own project and use it as is or take it as inspiration for your own conversion routines if you prefer one separate function for each type conversion.

Data types representing date and/or time values
Dates, times and combined date-times come up as UNO structs, as cell values in Calc sheets and Writer tables, form controls, dialog controls and quite often we struggle very hard with strings representing some date/time in a certain cultural context while nobody uses unambiguous ISO strings.
When you declare a variable as a StarBasic type of Date or when you call some StarBasic function that is documented to return a date/time value, then you get a specific StarBasic type of date value.
The following statements assign a StarBasic value of tye Date to the basDate variable:
Dim basDate As Date
basDate = DateValue("1999-12-31")
basDate = DateSerial(1999,12,31)
basDate = TimeSerial(23,45,59)
basDate = Now()
basDate = Today()

There are other Basic functions returning the numeric components of a StarBasic date such as Year(basDate), Month(basDate), Day(basDate).
Outside the StarBasic language, in table cells, in database fields and form controls, these StarBasic date/times can not be used. You will get an error when you assign a Basic date to a form control. You may end up with a text value when you assign a Basic date to a spreadheet cell.
 Edit: 2017-11-03: Meanwhile both method calls, oSheetCell.setValue(Now()) and oSheetCell.setValue(Date()), insert a correct value into a spreadsheet cell. This works with LibreOffice 5.4 and OpenOffice 4.1. 
You get an UNO Struct in most cases when you query a date/time from an UNO object.
Examples:
ThisComponent.NullDate --> com.sun.star.util.Date
objDatabaseField.getDate() --> com.sun.star.util.Date
ThisComponent.DocumentProperties.ModificationDate --> com.sun.star.util.DateTime
objFormControl.getDate() --> com.sun.star.util.Date in LO, 8 digit integer in AOO
objFormControl.getTime() --> com.sun.star.util.Time in LO, 8 digit integer in AOO
These UNO structs with their integer elements are documented here:
http://www.openoffice.org/api/docs/comm ... /Date.html
http://www.openoffice.org/api/docs/comm ... eTime.html
http://www.openoffice.org/api/docs/comm ... eTime.html
Unlike Basic functions such as Year(basDate) which returns the year portion from a Basic date, unoDate.Year is the year portion from an UNO struct. unoDate.Year = 2016 modifies the Year portion of a date struct. With a Basic date you'd have to rebuild the whole date value by means of a Basic function such as newDate = DateSerial(2016, Month(oldDate), Day(oldDate)).

Surprisingly, the last two examples with objFormControl will not return an UNO struct from an OpenOffice date/time control or from a LibreOffice3 date/time control. These applications use "strange integers" like 19991231 and 23455967 representing a date control value 1999-12-31 and a time control value 23:45:59.67 respectively. The integers are strange because most of the possible integer numbers are invalid. 20001349 is a valid integer but it can not be assigned to a date control because the digits do not represent a valid date (month #13, day #49). Likewise, 98768238 is a valid integer but its digits do not represent any valid time value.

Sooner or later all macro programmers notice that localised date/time strings are not convertible to spreadsheet values. This is not a Basic issue. It is an issue with all programming languages. Calc and StarBasic are able to convert very complicated string expressions into date values if the global locale option is set correctly in Tools>Options>LanguageSettings>Languages.
DateValue("1/2/1999") yields second of January in US English context and first of February with most other locale settings.
DateValue("1/23/1999") raises an error because there is no 23th month unless the global locale is US English.
DateValue("སྤྱི་ལོ ༡༩༩༩ ཟླ་ ༡༢ ཚེས་ ༣༡") returns 31th of December 1999 as a StarBasic value if the global locale is set to Dzongkha language.
DateValue("31. Dezember 1999") returns 31th of December 1999 as a StarBasic value if the global locale is set to German language.
In reverse, cStr(Date) may return any of the above strings (and more) depending on the locale being English, US-English, Dzongkha, German or something else.

ISO strings YYYY-MM-DD HH:MM:SS.00 work with all locale settings.
DateValue("1999-02-01") always returns the first of February 1999 as a StarBasic DateValue.
In reverse, format(Date, "yyyy-mm-dd") should return an ISO string since the format specifier seems to interprete English locale strings (y-m-d) independently from the locale setting.

Apart from the global locale option which is the base of all string to number conversion, it is inevitable that spreadsheet users (and macro programmers in particular) understand dates and times in table cells of Writer and Calc. All values in table cells are floating point numbers of type Double unless they are strings or error values. There are no dates nor times in table cells.. Not even integers. Floating point numbers with no digits behind the point are displayed as integers.
The cell values of type Double represent the days since day zero which is 1899-12-30 (StarBasic: ThisComponent.NullDate).
Cell value 40000.75 represents the 40000th day plus 0.75 of a day.
Cell value 40000.75 formatted as date-time is shown as 6th of July in 2009 at 6 p.m. which is exactly 40000.75 days from NullDate 1899-12-30 00:00:00.
In very rare cases (spreadsheets imported from exotic spreadsheet applications), the displayed dates are based on a NullDate other than 1899-12-30.
With 1904-01-01 as NullDate, the same cell value 40000.75 formatted as date-time is shown as 7th of July 2013 at 6 p.m.

Simple hands-on demo on a spreadsheet: Enter 0 in one cell and 40000 in another cell. Format both cells as date. The zero represents the NullDate, 40000 represents the 40000th day after the NullDate. Then switch the date setting in Tools>Options>Calc>Calculate to 1904-01-01 and see the result shifted by 2 days and 4 years. In the attached spreadsheet you see the standard NullDate and the current one in cells A8 and A9; the difference between them is calculated in A10. Since the user-defined Basic function always uses the regular NullDate 1899-12-30; it can't adjust by any means. The difference between the StarBasic NullDate and ThisComponent.NullDate needs some observance in all conversions from number to string and vice versa.

The attached document includes a StarBasic function ConvertDateTime on Module2 to convert Basic dates, doubles, strings, UNO structs and even "strange integers" into equivalent Basic dates, doubles, localised and ISO strings, UNO structs and even "strange integers".
The formulas on the sheet use ConvertDateTime as a user-defined cell function which is NOT the purpose of this function. Using it as a user-defined cell function is just another way to visualise how this function is supposed to work. On a sheet you can demonstrate operations with strings and doubles but not with UNO structs nor "strange integers" as input values since spreadsheets cells have no integers.

There is a push button attached to the sheet which calls Standard.Module1.Main. That StarBasic routine demonstrates how to utilize ConvertDateTime to read and write dialog control values, how to convert cell values into "strange integers" or UNO structs respectively, how to take ThisComponent.NullValue into account and how to convert "strange integers" or UNO structs back into correct cell values.

ConvertDateTime takes 2 arguments. The first one is a variant of any type ("strange integer", double, string, UNO struct or Basic date).
The second argument is a string describing the wanted return type ("INTDATE", "INTTIME", "UNOSTRUCT", "UNODATE", "UNOTIME", "DOUBLE", "STRING", "ISO" or "BASIC", not case-sensitive).
An optional third argument (default=False) is required for "strange integers" coming from time controls in OpenOffice or LibreOffice3. Set this argument to True in order to indicate that the integer argument represents a time. If the third argument is missing or False, any integer value is supposed to represent a date. Conversions from all types other than integer ignore the third argument.

In the following example intVar refers to a "strange integer" representing a date or time for an old style OpenOffice/LO3 dialog control.
AnyVar refers to an arbitrary type of variable (string, number, Basic date, UNO struct).
intDate = ConvertDateTime(intVar, "BASIC", False) converts a "strange integer" (19991230) coming from an old style OpenOffice/LibreOffice3 date control into a Basic Date type.
ConvertDateTime(intVar, "ISO", True) converts a "strange integer" (23453200) coming from an old style OpenOffice/LibreOffice3 time control into an ISO time string "23:45:32". The 3rd argument True makes this integer distinguishable from a "strange integer" representing a date. StarBasic can not deal with fractions of seconds.
ConvertDateTime(AnyVar, "INTDATE") converts any type into a "strange integer" for an OpenOffice/LibreOffice3 date control.
ConvertDateTime(AnyVar, "INTTIME") converts any type into a "strange integer" for an OpenOffice/LibreOffice3 time control.
ConvertDateTime(AnyVar, "UNODATE") converts any type into a com.sun.star.util.Date struct for a LibreOffice4 date control, database date columns and many other UNO objects.
ConvertDateTime(AnyVar, "UNOTIME") converts any type into a com.sun.star.util.Time struct for a LibreOffice4 time control, database time columns and many other UNO objects.
ConvertDateTime(AnyVar, "UNOSTRUCT") converts any type into a combined com.sun.star.util.DateTime struct representing a date and time for a database time stamp column and many other UNO objects.
ConvertDateTime(AnyVar, "ISO") converts any type into an ISO string "1999-12-30" or "23:45:59" or "1999-12-30 23:45:59"
ConvertDateTime(AnyVar, "STRING") converts any type into a localized string according to the global locale.
ConvertDateTime(AnyVar, "DOUBLE") converts any type into a floating point number as used in cells.
ConvertDateTime(AnyVar, "BASIC") converts any type into a Basic date-time type.
Attachments
StarBasic_ConvertDateTime.ods
Spreadsheet demo of StarBasic helper function ConvertDateTime to convert all possible representations of dates, times and time stamps in LibreOffice and OpenOffice simple types, Basic Date types, cells values and UNO structs.
(37.92 KiB) Downloaded 707 times
Last edited by Villeroy on Sat Nov 04, 2017 5:24 pm, edited 5 times in total.
Locked