User Guide > Scripting > Using Scripts to Perform Specific Tasks > Converting Dates
Was this helpful?
Converting Dates
Dates may appear in a wide variety of formats in data files. This table gives you source, target, and data type details, and what to do to convert dates for each type.
Source and Data type
Target and Data type
To Convert Dates
Pre-structured
Data type is "Date"
Pre-structured
Data type is "Date"
Maps usually convert dates to the correct format in the target file automatically. If not, re-format the date.
Delimited ASCII
Data type is "Text"
Pre-structured
Data type is "Date"
Write a target field expression using the DateValMask Function.
Binary, Fixed ASCII, or Record Manager
Pre-structured
Write a target field expression to re-format the date in your target date field.
Any type
Binary, Fixed ASCII, or Record Manager
Write a target field expression to re-format the date in your target date field.
Many pre-structured data files allow dates to be stored in only one format. For example, dBASE applications store dates in an "yyyymmdd" format. Binary and record manager data files may store dates as integers. Spreadsheets and ASCII files may contain dates formatted as "mm/dd/yy", "yy/mm/dd", "dd-mon-yyyy", or any of the other unlimited possibilities. Therefore, when converting dates from one application to another, some special handling may be required.
There are several functions for manipulating dates:
For a complete list of available functions, see Script Functions A-F, Script Functions G-M, and Script Functions N-Z.
This table gives examples for date transformation expressions.
Source Format
Target Format
Expression Example
Text field
dd-mon-yy mm/dd/yy
Text field
05-Jul-99 05/07/99
DateConvert("dd-mon-yy ", "mm/dd/yy", FieldAt("/SOURCE/R1/Field1"))
Two Text fields, one date, the other time:
Field1:
09/15/99
Field2:
14:37:09
SQL Server DateTime fields
Field1:
09/15/99
Field2:
14:37:09
DateValMask(FieldAt("/SOURCE/R1/Field1") & (FieldAt("/SOURCE/R1/Field1")), "mm/dd/yyH24:MM:SS")
Date Field from SQL Server table
To Binary Packed decimal
No date conversion expression is necessary. Using the default expression, a target field is converted to the date value as a packed decimal.
dBASE Date field
yyyymmdd
19980122
Text field
mm/dd/yy
01/22/98
DateConvert("yyyymmdd", "mm/dd/yy", FieldAt("/SOURCE/R1/Field1"))
PSQL DateTime
mm/dd/yyyy HH:MM:SS Am
Salesforce DateTime
YYYY-MM-DDTH24:MM:SS.000Z
Dim DateArray(2)
DateArray = Split(FieldAt("/SOURCE/R1/Field1"), " ")
DateConvert("m/d/yyyy", "yyyy-mm-dd", DateArray(0)) & "T" & TimeValue(DateArray(1) & " " & DateArray(2)) & ".000Z"
Salesforce DateTime
YYYY-MM-DDTH24:MM:SS.000Z
PSQL DateTime
mm/dd/yyyy HH:MM:SS Am
Dim inputDate
inputDate = DateValMask(StrReplace("Z", "", FieldAt("/SOURCE/R1/Field1")), "yyyy-mm-ddTH24:MM:SS.TT3")
Format(inputDate, "yyyy-m-d hh:mm:ss")
Text field
mm/dd/yy
Text field
mm/dd/yyyy
If Right(FieldAt("/SOURCE/R1/Field1"), 2) >= 50 Then
Left(FieldAt("/SOURCE/R1/Field1"), 6) & "19" & Right(FieldAt("/SOURCE/R1/Field1"), 2)
Else
Left(FieldAt("/SOURCE/R1/Field1"), 6) & "20" & Right(FieldAt("/SOURCE/R1/Field1"), 2)
End if
The last expression example above checks for a year value greater than 50, concatenates "19" if the year is more than 50, and concatenates "20" if less than 50. It is used to transform two-digit dates into four-digit ones. The 50 can be replaced with whatever number your company uses as the cut-off for year 2000 data conversions. It can also be varied according to what is logical for each field. For example, a birth date field would almost always need "19". An expiration date on a 10-year warranty field would be more likely to need "20".
The mask for three letter months, like Feb, is "Mon" for the DateValMask function, and "mmm" for the Format function. See DateValMask Function.
Date masks are lowercase and time masks are uppercase. MM indicates a two-digit minute; mm indicates a two-digit month.
Last modified date: 10/22/2024