Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Date/Time Conversion Issue

Author  Topic 

crownclit
Starting Member

20 Posts
Posted - 2010-10-13 : 18:46:59
Hi
I am not really sure how to convert following to usable date or time format.

Could you guys help?

This data is coming from cisco call centre sql database.

dnCallStartTime dnCallEstabTime dnCallStopTime
1286402877 1286402881 1286402975

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-10-13 : 18:50:05
It appears to be the number of milliseconds since 1970.

If that's the case, you can use this (which I wrote a while back): http://weblogs.sqlteam.com/tarad/archive/2003/11/10/473.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-10-13 : 18:51:29
Here's the tool that I used to check if your data was Epoch/Java Time: http://www.esqsoft.com/javascript_examples/date-to-epoch.htm

Since it came up with a valid date, I figured I was on the money.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

crownclit
Starting Member

20 Posts
Posted - 2010-10-13 : 19:38:47
Thank you tkizer for reply
Your Function is working great I can see date\time properly the only problem is that function returns same value [1970-01-15 21:20:02.000] no matter which value of those three listed above I use. Another thing is that my call centre does not receive any call after business hours. This is after 6pm. But this may be a separate issue.
Go to Top of Page

crownclit
Starting Member

20 Posts
Posted - 2010-10-13 : 19:43:19
tkizer
converter works perffect, from 1286402877 value i'm getting [Thu Oct 7 09:07:57 UTC+1100 2010]
i need to do same conversion in SQL
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-10-13 : 19:51:15
So your value and Epoch time are number of seconds since 1970. Use this instead:

CREATE FUNCTION udf_ConvertEpochTimeToDateTime
(@seconds BIGINT)
RETURNS DATETIME
AS

BEGIN

DECLARE @DateTime DATETIME

SELECT @DateTime = DATEADD(s, @seconds, '1970-01-01 00:00:00.000' )

RETURN @DateTime

END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-10-13 : 19:53:10
So Epoch time is number of seconds since 1970, and Java time (what my udf is converting in that link) is number of milliseconds since 1970.

Why can't people just use good old dates and times? What is the point of using 1970 as a base date? I just don't get it. It's rhetorical, I realize you don't have the answer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

crownclit
Starting Member

20 Posts
Posted - 2010-10-13 : 21:04:55
thank you tkizer
this works fine
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2010-10-13 : 21:35:28
ummmm...Arnold gave me the UNIX Epoch quite awhile ago

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2010-10-14 : 09:46:45
quote:
Originally posted by tkizer

So Epoch time is number of seconds since 1970, and Java time (what my udf is converting in that link) is number of milliseconds since 1970.

Why can't people just use good old dates and times? What is the point of using 1970 as a base date? I just don't get it. It's rhetorical, I realize you don't have the answer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog


If you are going to store seconds after 2038-01-19 03:14:07, you will need to revise the logic to use bigint, because an 32 bit integer will overflow.



select
	*,
	DT =
		case
		when UNIXTime between -6847804800 and 253402300799
		then dateadd(ss,UNIXTime%86400,(UNIXTime/86400)+25567)
		else null
		end
from
	( -- Test Data
	select UNIXTime = convert(bigint,-6847804800-1)	union all
	select UNIXTime = convert(bigint,-6847804800)	union all
	select UNIXTime = convert(bigint,2147483647)	union all
	select UNIXTime = convert(bigint,6147483647)	union all
	select UNIXTime = convert(bigint,253402300799)	union all
	select UNIXTime = convert(bigint,253402300799+1)	
	) a


Results:
UNIXTime             DT
-------------------- -----------------------

         -6847804801 NULL
         -6847804800 1753-01-01 00:00:00.000
          2147483647 2038-01-19 03:14:07.000
          6147483647 2164-10-21 10:20:47.000
        253402300799 9999-12-31 23:59:59.000
        253402300800 NULL

(6 row(s) affected)




CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-10-14 : 14:23:17
No worries, my code isn't in use in any of my systems. The functions were created to help us do the conversions on the SQL side with adhoc queries.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2010-10-14 : 15:33:48
I understand, I just wanted to post code to handle more general cases where it does matter. The code itself really isn't that much more complex and probably executes about as fast.

Here is the code to handle conversion of milliseconds since 1970-01-01 to DATETIME.
select
	*,
	DT =
		case
		when JavaTime between -6847804800001 and 253402300799998
		then dateadd(ms,JavaTime%86400000,(JavaTime/86400000)+25567)
		else null
		end
from
	( -- Test Data
	select JavaTime = convert(bigint,-6847804800001-1)	union all
	select JavaTime = convert(bigint,-6847804800001)	union all
	select JavaTime = convert(bigint,1202147483647)	union all
	select JavaTime = convert(bigint,6202147483697)	union all
	select JavaTime = convert(bigint,253402300799998)	union all
	select JavaTime = convert(bigint,253402300799998+1)
	) a



Results:
JavaTime             DT
-------------------- ------------------------
      -6847804800002 NULL
      -6847804800001 1753-01-01 00:00:00.000
       1202147483647 2008-02-04 17:51:23.647
       6202147483697 2166-07-16 02:44:43.697
     253402300799998 9999-12-31 23:59:59.997
     253402300799999 NULL

(6 row(s) affected)







CODO ERGO SUM
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site