Date, time, and number formats

You can control how dates, times, and numbers are formatted when you export data, or how they are interpreted when you import data. The default formats are determined by the Windows™ Regional Settings as set on your computer, if the default formats are unsuitable then you can configure the formats to use.

Note: The text qualifier, record, field, time, date, and number delimiters must generally be set to different characters.

Date formats

Date Order

The order in which to arrange the date parts. For example, Day, Month, Year, or Month, Day, Year.

Date Delimiter

The character that separates the date parts (day, month, year).

Month

A month can be formatted or interpreted:

  • As a number. For example, months are specified by their number; January = 01, February = 02.

  • Using an abbreviated name. For example: January = JAN, February = FEB.

: Note: The month format always defaults to MM (such as 01) even if the format in the Regional Settings is set to MMM.

Four-digit years

A year can be formatted or interpreted as either a four-digit or a two-digit number. When you are importing:

  • Turn off this option for two digits in the source data to be converted to four. The conversion is as determined by the Windows regional Date settings (specifically, the ‘When a two-digit year is entered, interpret as a year between’ setting). The digits that are used for conversion are the first (or only) two of the year in the source.

  • Turn on, for all year digits in the source to be used, if present. If not present, then a conversion is made from the digits that are present, as detailed above for 'off'.

: When you are exporting:

  • Turn on for all four-year digits to be copied to the export file.

  • Turn off for only the last two of the year digits to be copied to the export file.

Time formats

You can specify the format for times by changing the character (delimiter) that separates hours, minutes, and seconds. Valid characters include: colons (:), spaces, and periods (.). Do not use alphanumeric characters. The order of the time parts (hours, minutes, seconds) is determined by your Windows Regional Settings.

When importing, this determines how times are to be interpreted in the source file; and when exporting, this determines how to specify times in the destination file.

Attention: You should use the Auto Detect Formats option carefully, and verify your final results. This is because the AM/PM setting is not always detected correctly when the regional settings for AM/PM are set to blank. Also, there can be differences in how operating systems process date and time formats, if you are using a version of iBase later than 9.2.1. For example, a date of 31st January 1933, expressed as 33/01/31, might see the month incorrectly interpreted as 33 rather than 01. The wrong year might also be generated in your import because of how two-digit years are handled.

Number formats

You can specify the format for numbers by changing the character that is used as the decimal point (delimiter).

When importing, this determines which character is interpreted as being the decimal point in the source file. When exporting, this determines the character to be used as the decimal point in the destination file.

Note: In some regional settings, such as Russian, it is usual to use a non-break space character (<NBS>) as a delimiter for the thousands in a number format. This prevents the number from being broken up by a word wrap. When using the Import Wizard, the Date/Time & Number Formats derive the <NBS> character from the default locale settings of the Digit Grouping of the Regional and Language Options dialog.

If you change this character, iBase will not allow you to type it in (using the key combination Ctrl+Shift+Space). To re-enter this character, copy it from another application such as Microsoft™ Word, or from the Regional and Language Options dialog and then paste it into the Thousands separator box.