How to change the datatype BIGINT to date

The only way you can convert an integer type to a datetime is like this:

DateTime date = new DateTime(intValue);

A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond, or 10 million ticks in a second.

The value of this property represents the number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001 (0:00:00 UTC on January 1, 0001, in the Gregorian calendar), which represents DateTime.MinValue. It does not include the number of ticks that are attributable to leap seconds.

This can be done with the ALTER command. But that will fail if the database is not able to convert existing data to the new type which can be expected when converting from BIGINT to DATE.

Then you have to

• create a temporary table using the DATE type for the column to be converted
  
• copy the data from the original table using bulk copy with transformation
  
• drop the original table
  
• rename the temporary table to the original name
  

To implement the transformation you have to know how a date is represented in the BIGINT field so that it can be converted to a DATE. How to do this (and doing the whole above process) depends on the used database engine.