datetime to unix epoch time

Is there a function in SQL Server to convert a datetime into Unix Epoch time,
that is seconds elapsed since Jan 1 1970. The result will be an big integer.
Thanks.
Some sample:
Select datediff(ss,'19700101',Getdate())
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:3got4gFdjc93U1@.individual.net...
> Is there a function in SQL Server to convert a datetime into Unix Epoch
> time,
> that is seconds elapsed since Jan 1 1970. The result will be an big
> integer.
> Thanks.
>
|||And if you want a BIgint then
Select Convert(Bigint,datediff(ss,'19700101',Getdate()))
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:u7DRv0FbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Some sample:
> Select datediff(ss,'19700101',Getdate())
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
> news:3got4gFdjc93U1@.individual.net...
>

datetime to unix epoch time

Is there a function in SQL Server to convert a datetime into Unix Epoch time
,
that is seconds elapsed since Jan 1 1970. The result will be an big integer.
Thanks.Some sample:
Select datediff(ss,'19700101',Getdate())
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:3got4gFdjc93U1@.individual.net...
> Is there a function in SQL Server to convert a datetime into Unix Epoch
> time,
> that is seconds elapsed since Jan 1 1970. The result will be an big
> integer.
> Thanks.
>|||And if you want a BIgint then
Select Convert(Bigint,datediff(ss,'19700101',Ge
tdate()))
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:u7DRv0FbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Some sample:
> Select datediff(ss,'19700101',Getdate())
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
> news:3got4gFdjc93U1@.individual.net...
>

datetime to unix epoch time

Is there a function in SQL Server to convert a datetime into Unix Epoch time,
that is seconds elapsed since Jan 1 1970. The result will be an big integer.
Thanks.Some sample:
Select datediff(ss,'19700101',Getdate())
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:3got4gFdjc93U1@.individual.net...
> Is there a function in SQL Server to convert a datetime into Unix Epoch
> time,
> that is seconds elapsed since Jan 1 1970. The result will be an big
> integer.
> Thanks.
>|||And if you want a BIgint then
Select Convert(Bigint,datediff(ss,'19700101',Getdate()))
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:u7DRv0FbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> Some sample:
> Select datediff(ss,'19700101',Getdate())
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
> news:3got4gFdjc93U1@.individual.net...
>> Is there a function in SQL Server to convert a datetime into Unix Epoch
>> time,
>> that is seconds elapsed since Jan 1 1970. The result will be an big
>> integer.
>> Thanks.
>sql

datetime

i'm using datediff to get the elapsed time b/t a timestamp and the current time. at this point i'm putting the answer in minutes, but i would like to format it to be similar to HH:MM.
how do i do this in sql server??
thanks in advance
e3wittselect cast(datediff(mi,'05/28/2004',getdate())/60 as varchar)+':'
+cast(datediff(mi,'05/28/2004',getdate())-(datediff(mi,'05/28/2004',getdate())/60)*60 as varchar)|||SET ANSI_NULLS OFF
SET NOCOUNT ON
GO

if object_id(N'dbo.fn_ElapsedTime') is not null begin
drop function dbo.fn_ElapsedTime
print 'Function dbo.fn_ElapsedTime dropped'
end
go

CREATE function fn_ElapsedTime (
@.starttime datetime,
@.endtime datetime = Null)
returns varchar(40)
as
begin
declare @.d int, @.h int, @.m int, @.s int, @.ms int, @.dif1 int, @.ret varchar(40)
select @.d = 0, @.h = 0, @.m = 0, @.s = 0, @.ms = 0

set @.d = datediff(dd,@.starttime,@.endtime)
set @.dif1 = datediff(ms,dateadd(dd,@.d,@.starttime),@.endtime)

if (@.dif1 > 0) begin
set @.ms = @.dif1 % 1000
set @.dif1 = @.dif1 - @.ms
set @.s = ((@.dif1 / 1000) % 60)
set @.dif1 = @.dif1 - (@.s * 1000)
set @.m = ((@.dif1 / 60000) % 60)
set @.dif1 = @.dif1 - (@.m * 60000)
set @.h = ((@.dif1 / 3600000) % 60)
end

set @.ret = cast(@.d as varchar(25)) + ':' +
right('00' + cast(@.h as varchar(2)),2) + ':' +
right('00' + cast(@.m as varchar(2)),2) + ':' +
right('00' + cast(@.s as varchar(2)),2) + ':' +
right('000' + cast(@.ms as varchar(3)),3)

return @.ret
end
go

if object_id(N'dbo.fn_ElapsedTime') is not null begin
print 'Function dbo.fn_ElapsedTime created'
end
go|||ok... now it's working just the way i was wanting.

thank you.|||Don't forget about the modulo operator (%). It's hand for converting time values:

select cast(datediff(mi, [TimeStamp], getdate())/60 as int) + ':' + (datediff(mi, [TimeStamp], getdate()) % 60)

DateDiff

Gentlemen
I need to find ou how much processing time has elapsed from the
begining of a query to the end of a query and update a datetime database
field with the results. DateDiff() would give me each part ( hours, min,
sec) but it will not give it all together.
How would you do this?
MarkWouldn't it make more sense to store the number of units elapsed (e.g. 546
MS or 32.4 seconds) or the start and end time separately (from which you can
always get a duration)?
I question changing a duration to a datetime value, I think this will be
very misleading.
If a query takes more than 24 hours, how do you plan on storing that? What
is the date portion of your value going to be, 1900-01-01?
If it's always going to be less than 24 hours (who knows, with some of the
stuff I've seen here), you can use something like this. But I still think
this is not a very appropriate use for this kind of formatting.
http://www.aspfaq.com/2271
"Mark Moss" <markmoss@.adelphia.net> wrote in message
news:OZTeTAZuFHA.4080@.TK2MSFTNGP12.phx.gbl...
> Gentlemen
>
> I need to find ou how much processing time has elapsed from the
> begining of a query to the end of a query and update a datetime database
> field with the results. DateDiff() would give me each part ( hours, min,
> sec) but it will not give it all together.
> How would you do this?
>
> Mark
>