Convert string to datetime format in Crystal to show YYYY-MM-DD HH:MM:SS.MS
I have the following field (string) and want to convert it to a date/time field that will also show the milliseconds :- 2015-10-31 14:55:39.650 Is this supported in Crystal or will I have to go without the millisecond portion ?
Crystal Reports
Last Comment
John-S Pretorius
8/22/2022 - Mon
Mike McCracken
DO you need it as a datetime field for other purposes?
If you only need to display it then use the string to display.
What version of Crystal?
mlmcc
Mike McCracken
As far as I can tell, Crystal cannot convert the string with the milliseconds.
DO you need to sort on the field and milliseconds are significant in the sort?
mlmcc
John-S Pretorius
ASKER
Thank you for your feedback, my apologies for taking so long to respond. I was truly hoping to sort via the millisecond as these timestamps occur close to each other (between .050 and 0.780 ms) which means when I run Crystal I will see:
SQL PM_Time Crystal Time Movement
2015-10-31 14:55:39.250 2015-10-31 14:55:39 Transaction
2015-10-31 14:55:39.890 2015-10-31 14:55:39 Exit
When I sort I will see the Exit before the Transaction, so I converted the DateTime to a string in SQL to see it in Crystal (Varchar(24),ParkingMovements.time, 121) as PM_Time but I cannot sort properly as a string :-
What if I create a single value (number) and sort accordingly after doing some math with the string :-
This worked very well, thank you sir - what I found after digging deeper is that the actual application may be writing data to SQL with delays so an exit transaction (.450) sometimes occurs before a transaction (.230) , so what I did is to create another formula and give each transaction type (Entry, Transaction and exit) a value 0, 1 and 4 then sort by this.
I'm discussing with our developer why this is occurring in SQL.
Thank you very much for your assistance.
John-S Pretorius
ASKER
I thought it would interesting to show what I was referring to as this is a good find for me. The formula I use was :-
@MovementType
If ({PMovements.PM_MoveDesig} ='Entry' then 0 else if
({PMovements.PM_MoveDesig} ='Transaction' then 1 else if
({PMovements.PM_MoveDesig} ='Exit' then 4
So I then sort by Movement Type, the snapshot below shows the portion that I found for some reason writes the exit before the transaction in SQL even though the timestamp is before. 2015-11-10_8-53-51.gif
If you only need to display it then use the string to display.
What version of Crystal?
mlmcc