Convert a numeric column of dates into a date/time column

If a column of dates has a numeric data type, you can change the data type to date/time.

In This Topic

Use Change Data Type and Format Column to convert the data type
Use the Calculator to convert the data type

Suppose you have a numeric column of dates in YYYYMMDD format. In this format, the value 20150101 represents January 1, 2015. You want to convert the numeric column to a date/time column in MM/DD/YYYY format. There are two ways to do this: use Change Data Type and Format Column, or use the Calculator.

In the following examples, C1 has a numeric data type and contains dates in this format: YYYYMMDD. C2 is empty.

Use Change Data Type and Format Column to convert the data type

Use Change Data Type to change the data type of the column to text. Then use Format Column to change the data type to date/time and specify the date/time format.

Click in the column and choose Data > Change Data Type.
In Variables, enter the column.
From Choose type, select Text. Click OK.
Click in the column and choose Editor > Format Column.
From Choose type, select Date.
In Custom date format, type yyyymmdd. Click OK in each dialog box.
Click in the column and choose Editor > Format Column.
From Date display formats, select 01/23/2050. Click OK.

Use the Calculator to convert the data type

Using the Calculator, you can convert the data in one step. The result is stored in C2.

Choose Calc > Calculator.
In Store result in variable, enter C2.
Copy and paste the following into Expression: DATE(CONCATENATE(MID(TEXT(C1),5,2),"/",RIGHT(TEXT(C1),2),"/",LEFT(TEXT(C1),4)))
Click OK.