Is There a Way to Convert 'datetime' Format to 'timestamp' in Sql Server CE?
datetime, sql, sql-server-ce, timestamp, webmatrix
Solution
The UNIX_TIMESTAMP function does not exist in SQL Server on SQL Server Compact, but you can use DATEDIFF:
DATEDIFF(SECOND,{d '1970-01-01'}, ba_trans_entered)
Problem
I know there's a way to do this in regular Sql Server, and if I'm not mistaken, it looks something like this: ``` SELECT UNIX_TIMESTAMP(ba_trans_entered) * 1000 AS 'dateUTC' ``` I do admit, however, that I don't get the `* 1000` part, but that's beside the point. When I try to perform this query in SQL Server CE it just tells me (i.e., WebMatrix tells me): ``` 'UNIX_TIMESTAMP' is not a recognized built-in function name. ``` I'm assuming `UNIX_TIMESTAMP` is not supported in Sql Server Compact. Also, I tried Googling and searching here on SE but no data relevant to SQL Server CE shows up, so there may not be a way in the given environment. Is there any way to convert 'datetime' (example: `7/13/2007 12:00:00 AM`) to timestamp (example: `1184302800000`)? I know I can do this in JavaScript, but I was told it might be faster to do this in the query itself, and since I am pulling a ton of data...