Home » SQL & PL/SQL » SQL & PL/SQL » Server Date format
icon14.gif  Server Date format [message #362865] Thu, 04 December 2008 06:11 Go to next message
shanmp
Messages: 6
Registered: September 2008
Junior Member
Hi All,

I need to write a query to convert the server date type to sysdate.

When i run the GUI applications it gives the date as '00000000000000000001227773164825'

The server output for the sysdate (approximately) '04 12 2008 15:45:36' is '00000000000000000001227773164825'.

I am not sure which function i need to use to convert the datatype to the current sysdate format.

Please guide me Sad which function I would required to use this server date to the current sysdate format.

Thanks in advance
Shanmp
Re: Server Date format [message #362867 is a reply to message #362865] Thu, 04 December 2008 06:16 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Hope this will help you.

Rajy
Re: Server Date format [message #362869 is a reply to message #362865] Thu, 04 December 2008 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You first need to define what your string/number represents and then you can know how to convert from one to the other one.

Regards
Michel
Re: Server Date format [message #362870 is a reply to message #362867] Thu, 04 December 2008 06:23 Go to previous messageGo to next message
shanmp
Messages: 6
Registered: September 2008
Junior Member
To_date or To_char is not a proper function for my query. I have already tried it out.

For to_date it throws an error month should be between 1 and last day of month.
For to_char it throws an error invalid number format. Sad

The server date is displaying like the encrypted format , so I am not sure which function apart from this I can use it .

Thanks,
shanmp
Re: Server Date format [message #362871 is a reply to message #362869] Thu, 04 December 2008 06:25 Go to previous messageGo to next message
shanmp
Messages: 6
Registered: September 2008
Junior Member
Its defined as the string. (varchar(2))
Re: Server Date format [message #362873 is a reply to message #362871] Thu, 04 December 2008 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why '00000000000000000001227773164825' is '04 12 2008 15:45:36'?

Regards
Michel
Re: Server Date format [message #362875 is a reply to message #362873] Thu, 04 December 2008 06:32 Go to previous messageGo to next message
shanmp
Messages: 6
Registered: September 2008
Junior Member
I have run the report @ that particular time and date.
So the server date will be generated in a table which doesnt have the exact date format.
Now i need to convert that server date which is in string to a particular date format.

Re: Server Date format [message #362879 is a reply to message #362875] Thu, 04 December 2008 06:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to answer the following question.

What algorithm are we to use to convert the string '00000000000000000001227773164825' into the date '04 12 2008 15:45:36'

I would guess that it's something like milliseconds since a date, but if that's the case then we need to know the start date
Re: Server Date format [message #362881 is a reply to message #362879] Thu, 04 December 2008 06:45 Go to previous messageGo to next message
shanmp
Messages: 6
Registered: September 2008
Junior Member
I am not sure of the algorithm or start date used in it.
I am very new to the applications which I am working and the back end I could see they have used some user defined function ConvertRCNextRunTimes.

I am not sure of that function too since its an user defined function.
Re: Server Date format [message #362883 is a reply to message #362881] Thu, 04 December 2008 06:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, if it's a function on your system that you have access to and we don't, and you don't know how to do it, could you explain exactly what chain of mental processes lead you to believe that we would know how it was derived?

Try comparing several values of systimestamp with their eqivalent server values and see if you can spot a relation.
Re: Server Date format [message #362884 is a reply to message #362881] Thu, 04 December 2008 07:02 Go to previous messageGo to next message
shanmp
Messages: 6
Registered: September 2008
Junior Member
Hi All,

Its an user defined function and I have decoded by writing the PL/SQL procedure.

Thanks for the immediate response and thank u very much .

Re: Server Date format [message #362928 is a reply to message #362881] Thu, 04 December 2008 09:34 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
shanmp wrote on Thu, 04 December 2008 06:45
I am not sure of the algorithm or start date used in it.
I am very new to the applications which I am working and the back end I could see they have used some user defined function ConvertRCNextRunTimes.

I am not sure of that function too since its an user defined function.

What happened to peers these last few years?
How come people tend to prefer asking complete strangers over their colleagues when they have questions about a specific part of a specific application?!
Re: Server Date format [message #363353 is a reply to message #362928] Sat, 06 December 2008 00:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Frank wrote on Thu, 04 December 2008 07:34

What happened to peers these last few years?
How come people tend to prefer asking complete strangers over their colleagues when they have questions about a specific part of a specific application?!


Ah, that brings back memories. I am retired now but I remember having been on both sides of this problem.

For example, one time, when I asked a peer for something, his reply was, "My job is not to support what you do...", apparently not grasping the concept that I needed it in order to supply him with what he would need in the near future. Three months later when he wanted it that evening and expected me to work all night to do it, I reminded him of our prior conversation and informed him that this could not be done in one night and would likely be a three-month project, that failure to plan ahead on his part did not constitute an emergency on my part, and went home on time.

In another instance, I was ordered by a supervisor not to spend my time helping anybody with anything else until I had completed a rather lengthy project. Many of my peers received similar orders, so we found ourselves sitting in a sea of cubicles in silence, searching for what we needed online.

There were times when I had free time while waiting for things to finish running, but still wasn't allowed to go help anybody. So, I spent the time browsing forums and on a rather comical note, recognized some co-workers and responded to their questions there. I always used my hotmail email, not my work email connected with that account. Next thing I knew, I got a hotmail email from my boss, who said he saw some of my posts, was impressed with the answers, but wondered where I found the free time.



Re: Server Date format [message #363389 is a reply to message #363353] Sat, 06 December 2008 10:41 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, that was yet another Barbara's bedtime story. I enjoyed it, as always.

Just wondering - what was your colleague's reaction (story #1)?
Did your boss change his mind, eventually (story #2)?
Re: Server Date format [message #363392 is a reply to message #363389] Sat, 06 December 2008 12:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Quote:

Just wondering - what was your colleague's reaction (story #1)?



It was years ago, but as I recall we went back and forth a few rounds with him insisting he needed it now and me repeatedly explaining that if he needed it now, then he needed to give me what I needed to get started three months ago. I don't think it ever sunk in. This was not an isolated incident. He had this problem in multiple situations with multiple people. Our mutual boss eventually had a major meeting that was mandated by his boss to establish a time line and who needed what to get started on what. There were a lot of people sitting around with nothing to do for months, then scrambling at the last minute, because he waited till the last minute to take five minutes to give them what they needed to get started.

Quote:

Did your boss change his mind, eventually (story #2)?



We had many changes of management and policies and sometimes it was a little hard to even determine whose orders you were supposed to follow. I found myself assigned to one place, on loan to another place, working for one unit that was doing something for another unit, but told by someone who was given authority from a higher authority to have me work on something else. It got kind of confusing as to who was authorized to tell you to do what or not do what, due to the people above not making things or positions clear. At one point, it was decided to keep people's positions and salaries secret. That made it hard to tell who was entitled to give orders to who and whose orders you should follow. What a mess! I am glad to be done with all of that, but feel my talents were mostly wasted.







Re: Server Date format [message #363393 is a reply to message #363392] Sat, 06 December 2008 12:19 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, did person #1 got fired? It appears that he was the source of most problems.

What a mess! - exactly my thoughts while reading your words. Phew, it must have been a nightmare-like experience!
Re: Server Date format [message #363397 is a reply to message #363393] Sat, 06 December 2008 13:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Littlefoot wrote on Sat, 06 December 2008 10:19
So, did person #1 got fired?



No, he quit that unit and went to work at a different one.

Re: Server Date format [message #363403 is a reply to message #363397] Sat, 06 December 2008 16:54 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sure they were happy to have him Smile
Previous Topic: How to Fetch data on the basis of Unicode Characters
Next Topic: select rows
Goto Forum:
  


Current Time: Thu Feb 13 10:38:17 CST 2025