DATEADD Alternative for Conversion of Seconds

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 2005 Forums

Transact-SQL (2005)

DATEADD Alternative for Conversion of Seconds

Author

Topic

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 10:55:58

Hi,

I have a TIMESTAMP field that stores the number of seconds since 1/1/1990. The fields datatype is LONG.

Problem:

If the field were stored as an INTEGER then I could do something like SELECT DATEADD(s,617919719, '19900101') to get the actual date in a DATETIME format, but since it is long I can't use the DATEADD function as it only accepts an INT value.

Does anyone have or could direct me to some code that could help me convert seconds to a valid DATETIME signature???

For example, using 6179197190000.000

Any assistance is greatly appreciated!

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-01 : 11:26:20

6179197190000 secs would be a few thousand years from now, is that what you want to return?

Jim

Everyday I learn something that somebody else already knew

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 11:44:02

Hi Jimf

No. I don't want a thousand years from now but your statement did get a chuckle out of me for what its worth.

The definition of the field states that it is the number of seconds since 1.1.1990,0:00 GMT * 10000

To provide a bit more background on the issue. This field is stored in an SAP system using a SQL backend server as a long datatype. SAP has a function that takes this field and converts it to a DATETIME format. I need to derive a similar function in T-SQL that will do the same thing.

If I change the long value to an int and execute the following statement I get the datetime format I am after but it doesn't reflect the true datetime since I have to trim it to an INT value to work.

SELECT DATEADD(s,617919719, '19900101')
2009-07-31 20:21:59.000

Does this make the problem more clear?

I appreciate the response.
Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-10-01 : 11:54:41

This code shows how to do what you want. You need to be aware that the possible datetimes are 1753-01-01 00:00:00.000 through 9999-12-31 23:59:59.997, so the smallest and largest possible numbers you can convert this way are shown below.

select
	*,
	New_Datetime = dateadd(ss,a.dt_int%86400,dateadd(dd,a.dt_int/86400,'19900101'))
from
	( -- Test Data
	select dt_int = convert(bigint,-7478956800)	union all
	select dt_int = convert(bigint,617919719)	union all
	select dt_int = convert(bigint,252771148799)
	) a
order by
	a.dt_int

Results:

dt_int               New_Datetime
-------------------- -------------------------
-7478956800          1753-01-01 00:00:00.000
617919719            2009-07-31 20:21:59.000
252771148799         9999-12-31 23:59:59.000

(3 row(s) affected)

CODO ERGO SUM

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 12:30:21

Thanks Michael

That may just work, I'll just need to compare the results to the results returned by the SAP based function for validation.

I appreciate all the feedback guys!
Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-10-01 : 14:05:27

Just for fun, here is a slightly shorter piece of code to do it. Probably a bit harder to understand.

select
	*,
	New_Datetime = dateadd(ss,a.dt_int%86400,(a.dt_int/86400)+32872)
from
	( -- Test Data
	select dt_int = convert(bigint,-7478956800)	union all
	select dt_int = convert(bigint,617919719)	union all
	select dt_int = convert(bigint,252771148799)
	) a
order by
	a.dt_int

Results:

dt_int               New_Datetime
-------------------- -------------------------
-7478956800          1753-01-01 00:00:00.000
617919719            2009-07-31 20:21:59.000
252771148799         9999-12-31 23:59:59.000

(3 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources