| Author |
Message |
rlassalle
Groupie
Joined: 09 Aug 2012
Online Status: Offline
Posts: 57
|
   Topic: convert Dateime to string format
Posted: 29 Aug 2012 at 10:16am |
Hi Dell, I have .xls files as database, one the files is data-time like this 1/1/1900 2:33:07 AM in the formula bar in the cell showed 26:33:07. When i import it to crystal report it took date-time. How can I do if i want in the report that show the numbers like in the cell 26:33:07
In my report using a formuls totext I got this 02:33:07
My formula is:
Whileprintingrecords;
Local stringvar s_outTT;
s_outTT := totext({AGPInsvDb_.Outbound T T},"HH:mm:ss") but this is not what I want. wich format can use to get 26:33:07 ? The field is DateTime in my report.
Thanks for your help
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Aug 2012 at 10:27am |
your formual looks correct.
1. make sure you did not leave off one of the H's
2. If your string is aligned right, mkae usre it is wide enough to display the whole returned value
3. You do not have to convert it but rather right click on the original field (on the report canvas) and select Format Field, Time Format and select the 13:23:45 option
|
|
IP Logged |
|
rlassalle
Groupie
Joined: 09 Aug 2012
Online Status: Offline
Posts: 57
|
Posted: 29 Aug 2012 at 10:49am |
The filed show the whole value but it does not get the value that I have in .xls file. i did what you said but i got the same value than with the formula
This is the data in .xls 1/1/1900 2:33:07 AM (formula bar) with custom format [h]:mm:ss, showed in the cell
but in crystal show 2:33:07 after format it totext and I want the number like excel file
Thanks
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Aug 2012 at 10:55am |
i was not reading your post at a detail level.
26 is not a valid hour so how are you arriving at this value for the hour field?
Your sample data ... 1/1/1900 2:33:07 AM ... should have been
06:33:07
so I am confused by why you want it to show 26?
|
|
IP Logged |
|
rlassalle
Groupie
Joined: 09 Aug 2012
Online Status: Offline
Posts: 57
|
Posted: 29 Aug 2012 at 1:14pm |
The 26 is getting after you apply in the custom [h]:mm:ss
in the .xls 1/1/1900 2:33:07 AM (formula bar) I did some reserch and I found in excel this, Displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss.
In my case the formula has more than 24, given it is the result of a week hs worked. But, Crystal report import the field as DateTime.
I know is a little difficult. Other eg.
is the result of 1/2/1900 10:53:36 PM in the formula bar.
Is it possible do that in Crystal Report?
Thanks
Edited by rlassalle - 29 Aug 2012 at 1:15pm
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Aug 2012 at 2:33pm |
|
I think I see.
You can use a datediff function to get the number of hours from midnight and then use that + minutes and seconds in a string. You won't be able to use as a number or date or time field.
|
|
IP Logged |
|
rlassalle
Groupie
Joined: 09 Aug 2012
Online Status: Offline
Posts: 57
|
Posted: 30 Aug 2012 at 8:00am |
How could be the formula using datediff. My filed is DateTime name {AGPInsvDb_.Inbound AHT}. What is the midnight time?
Thanks
R
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Aug 2012 at 9:22am |
datediff('h',datetime(19020,1,1,0,0,0),{AGPInsvDb_.InboundAHT})
see what you get. I am not syure how your date is coming in so you may need to add 24 to this as it always assumes the 24 hours?
if this give you the correct hours you can use
totext(datediff('h',datetime(19020,1,1,0,0,0),{AGPInsvDb_.InboundAHT}),0,"") + totext({AGPInsvDb_.InboundAHT},":mm:ss")
|
|
IP Logged |
|
rlassalle
Groupie
Joined: 09 Aug 2012
Online Status: Offline
Posts: 57
|
Posted: 30 Aug 2012 at 12:57pm |
|
Thanks you, I will try
|
|
IP Logged |
|
rlassalle
Groupie
Joined: 09 Aug 2012
Online Status: Offline
Posts: 57
|
Posted: 30 Aug 2012 at 2:14pm |
Good, it is working adding the 24 hs with the values 1/1/1900 2:33:07 AM; but, the problem is that some time the filed bring different months like; 1/2/1900 10:53:36 PM or 1/3/1900 6:12:17 AM. So, the formula will not work when that happend.
Thanks
|
|
IP Logged |
|
|
|
