MS SQL Date Time to Epoch
August 25, 2008 at 4:14 am Australian Search Engine 6 comments
I know it’s been a while for you programmers out there, but here is an MS SQL function that converts Date Time to Epoch.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[fn_dateTimeToEpoch] (
@dateTime DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @epochDay BIGINT
DECLARE @epochSecond INT
– epoch ’01-01-1900 00:00:00′
– one day 86400 seconds
SET @epochDay = DATEDIFF( day, ’01-01-1900 00:00:00′, @dateTime )
– get the remainder
SET @epochSecond = DATEDIFF( second, ’01-01-1900 00:00:00′, DATEADD( day, -@epochDay, @dateTime ) )
– convert to seconds
RETURN ( @epochDay * 86400 ) + @epochSecond
END
Leave a message if you want the Epoch to Date Time function.
Entry filed under: MS SQL. Tags: .
Australian Search Engine
websitedesignaustralia
Taco Fleur
kyliewrites
cazazz
Jim Petrakis
1.
anon | August 14, 2009 at 9:19 pm
Epoch time is the number of seconds since January 1, 1970, not 1900.
2.
Luca | October 27, 2009 at 10:05 am
Thank you for this code, it’s really useful.
Just one thing: epoch starts @ 1970, not 1900, so you should replace ’01-01-1900 00:00:00′ with ’01-01-1970 00:00:00′ in your code.
3.
Australian Search Engine | October 27, 2009 at 10:37 am
Guys: an epoch means an instant in time chosen as the origin of a particular era. My instance is 1900
4.
Chris M. | April 8, 2010 at 1:53 pm
I work with MS Access, is there a similar function with VBA?
5.
Luca | September 6, 2010 at 9:15 am
Yeah, an epoch is from an arbitrary starting time, you coud have used 03-07-1931 12:03:57, but would that make sense?
Most “epochs” used in software and DBs are from 01-01-1970 00:00:00. And there’s a good probability that someone in need of an epoch value is actually interfacing with existing software…
6.
Chris M. | October 27, 2010 at 10:56 am
Thanks Lucas, I believe I was the originater for this request on ephoc, maybe not, but it’s of tremendous help to me.