Table of Contents
VDatabase DateTime Format
VDatabase have several properties that allow you to control how Valentina convert date-time values to/from strings:
Storage Format of DateTime
Valentina internally stores:
- Date value as LONG (4 bytes).
- Time value as ULONG (4 bytes).
- DateTime value as LLONG (8 bytes).
Date values are packed into bits of 4 bytes in the YYYYMMDD format. Time Values are packed into bits of 4 bytes in the HHMMSSms format. It is easy to see that such numeric format if platform-independent (well, except endian byte swapping of course).
Non-Persistance of Properties
Important to note, that
VDatabase.TimeSep properties ARE NOT stored inside of database files, i.e. they are not persistent properties. This is made with intention of easy work with the same database using different Date/Time Formats in a multi-user environment.
Local Database Example
Let some Valentina database files was sent to USA (MDY), Japan (YMD), Russia (MDY) countries. Let 3 users in that countries open these db files. Each will see date/time values in own format (default system).
Valentina Server Example
Let some database is located under a Valentina Server. Three users connect to VSERVER from USA (MDY), Japan (YMD), Russia (MDY). On connect the VCLIENT informs VSERVER about DateTime settings of this client. So each client get date/time strings in own format.
If one user CHANGES DateTime settings for a VDatabase, then other users DO NOT see these changes and continue to work in own formats. This is one more reason why date/time settings should not be persistent.
NOTE that each VDatabase have own date/time settings.
NOTE that a VCLIENT informs VSERVER about which DateTime format to use. So if your REALbasic client set YMD, this not means that, e.g. a VPHP client will see this format from that VDatabase. PHP client self must set format to YMD if it need that.
Change of DateTime Properties
You can change DateTime settings of VDatabase using either API or SQL way:
db.DateFormat = EVDateFormat.kYMD
SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD';
Ensure DateTime Format
If you develop some application, most probably you write in your code SQL strings using some DateTime format. To make sure that Valentina will understand it, you need after db.New() and db.Open() set date/time settings as your code expects.
NOTE: SQL Standard 1992 says that date literals should be in YMD format. Valentina do parsing of string literals in 2 steps. At first SQL Parser extract string literal “from quote to quote”. Then this string value is converted by engine to date/time value. On the second step date/time settings are used. So Valentina can work with any date formats.
When DateTime Settings Work
DateTime settings of VDatabase are used only and only when some Date, Time or DateTime value should be converter to/from string, i.e. this affects e.g.
- SQL strings. You send INSERT … VALUES( '2000-01-01' ), parser will extract date value as string, then engine will try convert it into date value using current date/time settings.
- functions VField.SetString() and VField.GetString(). Let you have some cursor with a Date, Time or DateTime field. This field still have values as NUMBERS. But if you ask value as string, then string conversions happens according to the current settings of the date/time format.
- Text import/export. Obviously this operation require string conversions.
Where DateTime Settings Work
Valentina can execute the string conversions on both SERVER and CLIENT sides.
Valentina Server executes the strings conversions when:
- parses SQL string of some SQL command
- do import/export on server side
- do xml dump/load on server side
- do triggers or stored procedures
Valentina Client executes the strings conversions when:
- you call VField.GetString() or VField.SetString() for a field of VCursor or VTable.
HOW VClient Get Info
For advance Valentina users.
You may wonder how Valentina Client, which send to VSERVER SQL string as
SET PROPERTY DateTimeFormat OF DATABASE to 'kYMD'
get back information that DateTime format was changed for it. We have implement this in v3.5.2 as additional information which Valentina.SqlQuery() method gets in response from VSERVER. So VCLIENT can effectively (once) update own settings of VDatabase.DateTimeFormat.
If you change date/time settings via API, then VCLIENT see these changes immediately.
Still there is problem with update of VCLIENT in case you send in single SQL query few commands as:
USE db1; SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD'; USE db2; SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD'; USE db3; SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD';
Problem is that VCLIENT will get back information about changes of date/time settings for active database only.
Workaround: do above code as three calls to Valentina Server.