I only created a function for this so that a simple name could be used. The
code itself is laughably just too darn easy to really require a function
though.

select dbo.epochtime(getdate())

Will give you the current epoch time.

select dbo.utctime(dbo.epochtime(getdate()))

The utctime converts the other way. This example just gives you getdate().


-----------------------
IF (OBJECT_ID ( 'epochtime', 'FN' ) IS NOT NULL )
begin
DROP function epochtime
end
go

create function epochtime
/*
author
jon baker
date
20070103
purpose
generate epoch time from provided date/time

modified -------------------------------------------------------------------
----------------------------------------------------------------------------
*/
(
@date datetime
)
returns bigint

as
begin
set nocount

declare @i bigint

set @i = datediff(second, '1970-01-01 00:00:00', @date)

return (@i)

end
go



IF (OBJECT_ID ( 'utctime', 'FN' ) IS NOT NULL )
begin
DROP function utctime
end
go

create function utctime
/*
author
jon baker
date
20070119
purpose
generate utc time from provided epoch time

modified -------------------------------------------------------------------
----------------------------------------------------------------------------
*/
(
@date bigint
)
returns datetime

as
begin

declare @i datetime

set @i = dateadd(second, @date, '1970-01-01 00:00:00')

return (@i)

end
go
-----------------------




-----Original Message-----
From: Tom Levey
[mailto:mssqldba-ezmlmshield-x91604175.[Email address protected]
Sent: Tuesday, January 30, 2007 5:21 PM
To: LazyDBA Discussion
Subject: SQL Server Time to Epoch

Hi all,



Can anyone help with a short SQL statement that lets me convert the System
time (presumably using getdate() ) to epoch time?



I have seen a post regarding the opposite (epoch to SQL date format) but I
am unsure how to switch thing around?



This will be run by a postgres DB (the SQL will hopefully be passed from a
*nix command line to the DB (if I can figure that one out) so it may be
possible to get the date from the *nix box and include that in the SQL
statement but I would prefer to perform the operation in one go with a
single SQL statement if possible!



All help greatly appreciated



Regards

Tom





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



MS Sql Server LazyDBA home page