| Author |
Message |
Zorgov
Newbie
Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
|
   Topic: strange datetime format
Posted: 23 Apr 2008 at 7:17am |
Hi everyone,
I would need some help on a problem I have.
In the database I am reading, the date field is presented like this :
DDDD HH:MM:SS
so it can appear like this : 23:42:28 or like this 188 23:42:28.
The problem I have is when I read the database with crystal, it doesn't show the days, but only the time. I know that in crystal 8, we had a feature in the options that could convert datetime data to string... maybe it could help, but I don't find it in my crystal 11.
P.S. there is no way I can change de database itself...
If you could help me, I would be grateful! 
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 23 Apr 2008 at 8:13am |
Does Crystal see this as a Date field or as a string field? If it sees it as a date, you could try using To_Text() in a formula to convert the datetime to a string and then use the formula on your report.
If it sees it as a string, you'll have to convert the '188' to a date in a formula.
-Dell
|
|
|
|
IP Logged |
|
Zorgov
Newbie
Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
|
Posted: 23 Apr 2008 at 9:57am |
I presume crystal see it as a Date fied : when i right click on it and I choose format field, I have a Date and Time tab.
I tried to convert to a string, but it gives me the exact same value.
for an exemple, here are the values i see :
in crystal, as a date field, I see : 3:21:33AM, as a string field I see the same exact thing.
but in my database (service center) , the real value is 118 03:21:33 .
seems like crystal dont "read" the day value...and keep only the time value.
can you help me with this? 
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 24 Apr 2008 at 6:35am |
How does "118" convert to a date? In other words, what day does it represent?
-Dell
|
|
|
|
IP Logged |
|
Zorgov
Newbie
Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
|
Posted: 24 Apr 2008 at 7:25am |
It's not really a day, it's a number of days... it means 118 days. Service center is an application that is used by helpdesks for ticketing incidents and requests. So if an incident is open and closed, the clock will show the total time in number of days, hours, minutes and seconds. You will then see in the clock database something like 118 34:35:46. I don't know if this date format even exist, so maybe Crystal doesn't recognize it like a date...
I hope this will help you understand my problem!  |
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 24 Apr 2008 at 7:37am |
It doesn't recognize it like a data because it isn't really a date. What "flavor" of database is this? (e.g, MS SQL Server, Oracle, etc.)
-Dell
|
|
|
|
IP Logged |
|
Zorgov
Newbie
Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
|
Posted: 24 Apr 2008 at 11:16am |
|
omg, it's P4 ... it's an homemade database made by Peregrine (Service Center owner)... i don't think it will help then. I will try to find the real type of the brute data. I know that it will be Oracle in the next Service Center version, maybe I will have to wait until there.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 24 Apr 2008 at 11:43am |
In Oracle it will probably end up being a varchar field - Oracle doesn't do funky dates like this - at which point you'll be able to display it with no problems.
-Dell
|
|
|
|
IP Logged |
|
Zorgov
Newbie
Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
|
Posted: 24 Apr 2008 at 12:58pm |
Thanks Hilfy! I will try to find a way to do a workaround for now and wait till the upgrade of my application in early 2009.
cya around!
|
|
IP Logged |
|
|
|
