[SOLVED] Convert timestamp (int) to smalldatetime (t-sql)

Convert timestamp (int) to smalldatetime (t-sql)

I have a MySQL-Database with several rows. In that environment, I stored the current time as a Timestamp (int).

Now I need to migrate my data from a MySQL to a T-SQL-Database. I'm running SQL-Server 2008.

I checked several approaches, but couldn't come up with a way which transforms my int into a smalldatetime format.

Is there a build-in function for this? Is this even doable alone in a statement? I really don't want to write a PHP-snippet, which converts the timestamp to the desired format.

Thanks in advance

Solution

As per the documentation, smalldatetime uses the following format:

DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';

So, we need to convert the MySQL timestamp into date and format it in the above format to get smalldatetime. This can be done by using FROM_UNIXTIME and DATE_FORMAT functions of MySQL, e.g.:

DATE_FORMAT(FROM_UNIXTIME(timestamp_column), '%e %b %Y') AS 'smalldatetime';

Here's the SQL Fiddle.