Print Page | Close Window

convert Dateime to string format

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17449
Printed Date: 06 May 2024 at 7:14am


Topic: convert Dateime to string format
Posted By: rlassalle
Subject: convert Dateime to string format
Date 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


Replies:
Posted By: DBlank
Date 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

Posted By: rlassalle
Date 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
26:33:07
 
but in crystal show 2:33:07 after format it totext and I want the number like excel file 
26:33:07
 
Thanks
 

Posted By: DBlank
Date 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?

Posted By: rlassalle
Date 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.
70:53:36
 is the result of  1/2/1900  10:53:36 PM in the formula bar.
Is it possible do that in Crystal Report?
 
Thanks 

Posted By: DBlank
Date 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.

Posted By: rlassalle
Date 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

Posted By: DBlank
Date 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")

Posted By: rlassalle
Date Posted: 30 Aug 2012 at 12:57pm
Thanks you,  I will try

Posted By: rlassalle
Date 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

Posted By: rlassalle
Date Posted: 30 Aug 2012 at 2:44pm
Sorry.  I answered so fast.  It is working well does not matter the months.
 
Thanks good work!


Print Page | Close Window