Prev: Nevron Gauge for SQL Server Reporting Services
Next: sp_send_dbmail href tag
From: Janet on 18 Jan 2010 11:53
I've been running the following to convert SQL time into Unix time:
select DATEDIFF(s, '19700101', end_date), end_date, start_date,
event_sub_type, courseid
from training.dbo.classes
where courseid like 'HLH 110.R031%'

However, it isn't taking into account the timezone - I need the unix
timestamp to take into account Eastern Time (GMT -5) so that when
translated taking into account timezone it comes out as such.

For example, given the SQL timestamp 2010-01-14
my script returns 1263427200 (when translated as GMT this is Jan 14,
but with the -5 for ET, it comes out to Jan 13)
but I need it to return something like 1263445200 (which with the -5
for ET, comes out to Jan 14)...

any ideas on how to do this in SQL? I only need to run it once.


From: Plamen Ratchev on 18 Jan 2010 12:05
You can add the 5 hours with DATEADD:

SELECT DATEDIFF(s, '19700101', DATEADD(HOUR, 5, end_date)),
end_date,
start_date,
event_sub_type,
courseid
FROM training.dbo.classes
WHERE courseid LIKE 'HLH 110.R031%';

--
Plamen Ratchev
http://www.SQLStudio.com
From: Janet on 18 Jan 2010 12:16
So easy and clean! Perfect! Thanks!
 | 
Pages: 1
Prev: Nevron Gauge for SQL Server Reporting Services
Next: sp_send_dbmail href tag