Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

Calculating date/time from Unix time

Our challenge is as follows:

A third party application writes phone call logs to an SQL Server 2000 database. The start and end times are unfortunately inserted as Unix time (seconds from 1-1-1970).

We want to make a monthly csv export of these records by selecting the detail records between certain dates/times but we are having problems calculating the correct date/timestamp as there seems to be no function in SQL server that can do this.

In stead of dumping the whole logfile (which is pretty big already) and making the selection in Oracle we would like to know if there is an easy way to do this with an SQL select statement on the database ?

Thank you in advance for your suggestions.

[708 byte] By [M.Lohmeijer] at [2008-2-13]
«« How to check for updates programmatically
»» SQL Server 2005 Express April CTP
# 1
First of all you are on the wrong forum, check out the Transact-SQL (T-SQL) forum.

In the meantime, you may want to look at Books Online (BOL) and check out the CONVERT function and see if there's anything there that will help you. If not you can quite easily acheive this using some string manipulation functions of which there are plenty.

If you take the second of these options and you are using SQL2005 you may want to look at using a SQLCLR function which will carry out this operation quicker than a T-SQL function will.

-Jamie

JamieThomson at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified