Migrating MySQL Unix timestamps to MSSQL date time

Just this week I've been migrating data held in a MySQL database to a Microsoft SQL database, and I've run into a bit of a headache with temporal values being stored as integers.

MySQL is a great database for the web. It has some nice features, one of which being the ability to export the table structure and all the data into one script you can download. It can even be zipped on the way. This makes transfering data super simple.

Super simple that is unless dates and times are stored as Unix timestamps, and its going into a MSSQL database.

Okay, so there is a certain amount of cleanup needed in the scripts going from one database to the other, but thats fairly easily done with an amount of regex.

However, dates and times stored as int are in Unix time (or POSIX time). So to get the actual date time of the value in MySQL you need to do something like:

SELECT FROM_UNIXTIME(date_added) FROM NewsStory

There isn't an equivalent of FROM_UNIXTIME in MSSQL so to get at the date time value from there you need to add to the value the number of seconds since the Unix epoch (1st January 1970 00:00:00), like this:

SELECT DateAdd(ss, date_added, '1970-01-01') FROM NewsStory

Et viola! Something sensible you can use.