MS SQL Date Time to Epoch

August 25, 2008 at 4:14 am 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.

Advertisements

Entry filed under: MS SQL.

Business Achiever Award Nomination Giving small businesses a big presence online

6 Comments Add your own

  • 1. anon  |  August 14, 2009 at 9:19 pm

    Epoch time is the number of seconds since January 1, 1970, not 1900.

    Like

    Reply
  • 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.

    Like

    Reply
  • 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 😉

    Like

    Reply
  • 4. Chris M.  |  April 8, 2010 at 1:53 pm

    I work with MS Access, is there a similar function with VBA?

    Like

    Reply
  • 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… 🙂

    Like

    Reply
  • 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.

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 19 other followers

Archives

Top Rated

Top Clicks

  • None

Blog Stats

  • 168,726 hits

%d bloggers like this: