Link to home
Start Free TrialLog in
Avatar of John-S Pretorius
John-S PretoriusFlag for United States of America

asked on

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 ?
Avatar of Mike McCracken
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?

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?

Avatar of John-S Pretorius


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 :-

tonumber(mid({PMovements.PM_Time},6,2)), +
tonumber(mid({PMovements.PM_Time},9,2)), +
tonumber(mid({PMovements.PM_Time},12,2)), +

I realize this sounds crazy but I don't know how else to get the sorting in a propper ascending fashion without having the milliseconds.
Avatar of Mike McCracken
Mike McCracken

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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 :-

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.