Determining the internal date format

date_001

Last week I had an interesting discussion with Kevin Frank.
We were trying to figure out what would be the best way to insert or update dates when using the myFMbutler DoSQL plug-in.

Before we go and look at the solution, let’s give you a little bit of background about how the plug-in works. When entering date and time values, you must match the format of the database file locale. This is the system format used on the machine where the file was created.
For example, if the database was created on a Belgian system, you would need to use the Belgian date and time format.

There can be a big difference between the format returned from a calculation by the calculation engine and the format that will be required for the plug-in environment.

An Example
Let’s pretend we have created a file on a machine with a US system and we want to use the file in an international environment, so Europeans can view and enter the dates in their own date format. To do so we will set the following File Option: “Always use current system settings”.

date_002

US System
Date(1 ; 31 ; 2010) returns 01-31-2010 (MM-DD-YYYY)
Format required for INSERT or UPDATE: {01-31-2010} {MM-DD-YYYY}

Belgian System
Date(1 ; 31 ; 2010) returns 31-01-2010 (DD-MM-YYYY)
Format required for INSERT or UPDATE: {01-31-2010} {MM-DD-YYYY}

On both systems, you need the use the US format when using the plug-in.

But if the file was originally created on a Belgian system, the situation would be different:

US System
Date(1 ; 31 ; 2010) returns 01-31-2010 (MM-DD-YYYY)
Format required for INSERT or UPDATE: {31-01-2010} {DD-MM-YYYY}

Belgian System
Date(1 ; 31 ; 2010) returns 31-01-2010 (DD-MM-YYYY)
Format required for INSERT or UPDATE: {31-12-2010} {DD-MM-YYYY}

An additional problem that may arise is when a user makes an empty clone or a recovered version of your file. When making an empty clone or when recovering a file, the original system formats will be removed. The first system that opens the new file, will save its system format to the file.

Say you have a solution that was originally created on a US system, and all DoSQL statements in that solution are using the {MM-DD-YYYY} format, if a Belgian user makes an empty clone of your solution and opens it on his machine, the solution will break.

Determine the Date Format
You can determine the file’s date format with the following calculation:

Case (
not EvaluationError(mFMb_DoSQL("SELECT {1/31/2010}")); "MMDDYYYY" ;
not EvaluationError(mFMb_DoSQL("SELECT {31/1/2010}")); "DDMMYYYY";
not EvaluationError(mFMb_DoSQL("SELECT {2010/31/1}")); "YYYYMMDD";
not EvaluationError(mFMb_DoSQL("SELECT {2010/1/31}")); "YYYYDDMM";
"????????"
)

Solution number two
Another way to insert or update a Date, is by using the DATE function. When you use the date function, you must always use the ISO format YYYY-MM-DD.

UPDATE FROM table SET myDateField = DATE ‘2009-01-31’;

Bonus: Determining the Decimal separator
Another problem that you can face in International projects is to determine the decimal separator. There is an easy calculation for that:

Middle( PI ; 2; 1)

Leave a Reply

You must be logged in to post a comment.