|
DateTime |
  
|
|
DateTime |
|
The first thing you should understand is that DMC will 'see' a DateTime SQl column like this :
STRING(8)
GROUP(8),OVER STRING -> (this column will NEVER be displayed and is hidden)
DATE
TIME
Depending on your Format, you have the choice of "working" on either the DATE and TIME "columns" seen or directly on the STRING column (SQL or HyperFile for example)
IF the Format is a DATE then use the DATE column and the mapping option to CONVERT to a DATE
IF the Format is a DateTime (like in HyperFile) then use the STRING column directly and the mapping option to CONVERT to DATE and-or the TIME portions (you need NOT use both)
This option - reserved for DATETIME or DATE columns or for CSV exports and cloning - will let you define to use or not the DATE col. and the TIME col. when in your Destination table you have a GROUP defined (TPS & SQL & HyperFile)
You check or not the "Use Date" and or "Use Time"
You can also check the "Use TODAY()" and or "Use CLOCK()" so as to fill in default values with the result of these functions
You can also define the FORMAT to be used by selecting one in the drop down box.
You otherwise select from the respective drop down boxes the DATE or LONG Columns in the Source Table to be used (as other Formats are incompatible)
When in 'ODBC' destination DB, the tool will automatically prohibit you from using the DATE and TIME columns found.
When in any other DB destination types, the tool will warn you to be careful of what you do with these mappings
Depending on the destination driver the TIME checkbox and details will or not be available (Oracle -FireBird and PostGre SQL do not allow DATETIME columns)
DATETIME groups and "Link by Name" process :
You can use the "Link by Name" button even when you have DATETIME columns in GROUPS. DMC will automatically - depending on the destination type of transfer - map or not the DATE and TIME columns, never the GROUP columns and the STRING columns will be filled when in ODBC accordingly with source DATE and TIME columns
To see what are the DATETIME columns we have provided a visual indicator in different icons of a calendar or a clock or a 30 calendar day in both panels
This screenshot is BEFORE any mapping is done
As you can see in this example of TPS -> SQL transfer all GROUP columns are grayed out and have a forbidden icon on them - all DATE columns have a calendar 30 days icon - all TIME columns have a clock icon and the STRING column has another type of calendar icon.
Now after using the "Link by Name" button this will show the same columns NOT being mapped (in ODBC a DATE and a TIME column do not exist in a DATETIME column) :
But the STRING column (containing the real DATETIME) is mapped as a DATETIME also for DMC :
And this is the detail of the STRING mapping done automatically by DMC
As you can see the DATETIME is checked and BOTH Use Date and Use Time are also checked, plus both Source columns are pre filled for you.
Double-click on those columns and see or set the mappings as this is defined (with or without the TIME part depending on your needs)
If you are doing a to NON ODBC transfer then the process will be different : The STRING column will be mapped normally but both the DATE and TIME columns will have the same DateTime mappings individually
This technique enables you to do your data Transfer in most cases directly without bothering about your DATETIME GROUPS (still as all cases are maybe not covered, do take a look before transferring the data to check it corresponds to your needs)
When exporting (cloning) data from CSV text tables (or any other Table) , if you have a DATE or a TIME column in the source File stored in a STRING column, you are able with this DateTime option to define very specific details required by DMC to achieve a perfect data transfer
1. Check DateTime or date or Time
2. For a Date check Use date (or use Time for a Time column)
3. Select in the drop down on the right the DATE or TIME FORMAT which is used in the source column and a DEFORMAT will be performed using this value
4. Select from the drop down box the source Table column to link to this column you are working on
That is all DMC needs to know to be able to translate your TEXT DATE to a real DATE column (generally a LONG column type)
You can also define the DESTINATION Date or Time FORMAT to use
Here are the equivalences of the DATE FORMATS
Picture Format Result
D1 mm/dd/yy 10/31/59
D2 mm/dd/yyyy 10/31/1959
D3 mmm dd,yyyy OCT 31,1959
D4 mmmmmmmmm dd, yyyy October 31, 1959
D5 dd/mm/yy 31/10/59
D6 dd/mm/yyyy 31/10/1959
D7 dd mmm yy 31 OCT 59
D8 dd mmm yyyy 31 OCT 1959
D9 yy/mm/dd 59/10/31
D10 yyyy/mm/dd 1959/10/31
D11 yymmdd 591031
D12 yyyymmdd 19591031
D13 mm/yy 10/59
D14 mm/yyyy 10/1959
D15 yy/mm 59/10
D16 yyyy/mm 1959/10
D17 Windows Control Panel setting for Short Date
D18 Windows Control Panel setting for Long Date
When you select D0x it will add a leading 0 like this
@D01 mm/dd/yy 01/01/95
Alternate separators
@D1. mm.dd.yy Period separator
@D2- mm-dd-yyyy Dash separator
@D5_ dd mm yy Underscore produces space separator
@D6` dd,mm,yyyy Grave accent produces comma separator
Here are the equivalences of the TIME FORMATS
Picture Format Result
T1 hh:mm 17:30
T2 hhmm 1730
T3 hh:mmXM 5:30PM
T03 hh:mmXM 05:30PM
T4 hh:mm:ss 17:30:00
T5 hhmmss 173000
T6 hh:mm:ssXM 5:30:00PM
T7 Windows Control Panel setting for Short Time
T8 Windows Control Panel setting for Long Time