Symptom: I am changing the Data Type of a Date/Time field and the new date and/or time displayed is incorrect. For example:
You have a Date/Time field with the value: 06/04/2017 09:00
You change the Data Type from Date/Time to a Character field, the value displays as: Apr 6 2017 8:00AM
or
You have a Date/Time field with the value: 06/04/2017 00:00
You change the Data Type from Date/Time to a Character field, the value displays as: Apr 5 2017 11:00PM
Product Details:
Product Family: Act!
Product: Act! Premium
Version: v19.0 and later
Environment:
Engine: Microsoft SQL Server
You have a Date/Time field with the value: 06/04/2017 09:00
You change the Data Type from Date/Time to a Character field, the value displays as: Apr 6 2017 8:00AM
or
You have a Date/Time field with the value: 06/04/2017 00:00
You change the Data Type from Date/Time to a Character field, the value displays as: Apr 5 2017 11:00PM
Product Details:
Product Family: Act!
Product: Act! Premium
Version: v19.0 and later
Environment:
Engine: Microsoft SQL Server
Cause:
This is due to the way Microsoft SQL handles the data. The time/date held in SQL does not reflect time zone adjustments in your region, i.e. the clocks moving forward/back one hour.
Solution:
- Backup your existing database following the steps in the article below:
How to back up and restore an Act! database
- Export the data to Excel following the steps in the article below:
How to export data from Act! to Microsoft Excel
- Change the Data Type of your field following the steps in the article below:
How to Change the Data Type of a Field Without Losing Data
- Import the data from Excel following the steps in the article below:
How to Import Contact Data from an Excel®, Comma Delimited (CSV), or Text Delimited (TXT) File into Act!
- When prompted select the Import type as Custom
Important: As the field already contains data you will have to select Replace with the source contact to overwrite the existing values
- Once the import is complete check and verify the data has imported correctly.