Prev: Login Failed Reason: Token-based access failed
Next: [sys.dm_db_index_usage_stats].[user_lookups]
From: Janet on 8 Sep 2009 17:14
Given the following timestamp in MySQL Unix Time:
1259294400

If I run the following in MySQL I get the date/time below (which is
correct):
SELECT from_unixtime(sessdate), id FROM mdl_attendance_sessions;
2009-11-26 23:00:00

If I run the following in SQL, I get the date/time below (which is
incorrect):
select DATEADD(s, dbo.view_mdl_attendance_sessions.sessdate,
'19700101') as sessiondate
from view_mdl_attendance_sessions
2009-11-27 04:00:00.000

I need it to convert in SQL to the same date as is shown in MySQL. Is
there a more accurate function I can use to give me the date from a
unix timestamp in SQL? I don't care about the time as much as having
the correct date is important.

Any help would be appreciated.
From: Plamen Ratchev on 8 Sep 2009 17:50
The formula that you have for converting UNIX timestamp in SQL Server is correct. Maybe the MySQL function uses current
time zone settings to do some conversion. Try with a couple different values to check if the difference in hours is
consistent.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Janet on 8 Sep 2009 18:14
Is there an easy update I can run to set the timezone in SQL to
Eastern Standard Time?
From: Plamen Ratchev on 8 Sep 2009 18:33
SQL Server takes the time zone from the operating system, so you need to adjust you operating system time zone.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 9 Sep 2009 09:51
Janet (ckauvar(a)gmail.com) writes:
> Is there an easy update I can run to set the timezone in SQL to
> Eastern Standard Time?

I guess the problem is that the Unix timestamp is in UTC. Since SQL Server
does not know about this, it will perform the computation accordingly.

This will be a little better:

select DATEADD(s, dbo.view_mdl_attendance_sessions.sessdate +
datediff(HOUR, getdate(), getutcdate()),
'19700101') as sessiondate
from view_mdl_attendance_sessions

That is, you need to add your offset to UTC to the Unix timestamp.

Alas, this does not take DST in consideration, so the value will still
be one hour wrong if you run it today.

If you are on SQL 2008, there is still a chance to do this right in
T-SQL only. If you are on SQL 2005, you can only solve this in a CLR
stored procedure where you have access to more sophisticated date/time
manipulation.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 |  Next  |  Last
Pages: 1 2
Prev: Login Failed Reason: Token-based access failed
Next: [sys.dm_db_index_usage_stats].[user_lookups]