Date conflict - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Date conflict

Author

Topic

funnyme.25
Starting Member

3 Posts

Posted - 2009-04-30 : 16:42:21

Im unable to convert timestamp from 1241091011533 , 1241091010797 etc.
Dont know how will we cast it....

Result should be something like

Wed Jan 30 11:22:04 GMT 2009 from 1241091011533

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-30 : 16:58:53

can u explain how 1241091011533 becomes "Wed Jan 30 11:22:04 GMT" ...

funnyme.25
Starting Member

3 Posts

Posted - 2009-04-30 : 17:13:34

Dont know ,we have a column with datatype as XML and inside this xml the node value has time stamp as

<JMSTimestamp>1240918212230<JMSTimestamp>

Now how do we get actual date from this node. "Wed Jan 30 11:22:04 GMT" is not the exact result. Requirement is to convert this node to actual date and time.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-30 : 17:57:25

Even if we provided a suggested conversion, how would you know if it were correct if you don't know what that value represents?

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-30 : 18:02:16

According to a quick google, the JMSTimestamp is:
The value is in the format of a normal millis absolute time value in the Java programming language, which is the difference, measured in milliseconds, between the a given time and midnight, January 1, 1970 UTC.

If that is the case then maybe something liek this would work:

SET @MiliSeconds = 1241091011533
SELECT DATEADD(MS, @MiliSeconds % 1000, DATEADD(SECOND, @MiliSeconds / 1000, '19700101'))

Although this is months away from what you posted it should be, I think the math is correct. So, maybe someone more versed in cross platfor date math can chime in..

funnyme.25
Starting Member

3 Posts

Posted - 2009-05-01 : 11:13:11

Thanks Lamprey,

But im unable to use dateadd function...
Can you please help..

select DATEADD(ms,12411774111840,DATEADD(ss, 1241177411184/1000, '19700101')),data from <table_name>

Error-
SQL0206N "MS" is not valid in the context where it is used. SQLSTATE=42703

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-02 : 23:24:08

quote:
Error-
SQL0206N "MS" is not valid in the context where it is used. SQLSTATE=42703

Looks like you are using DB2 and not Microsoft SQL Server. Do note that this is a MS SQL Server forum

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources