Convert string to datetime format in Crystal to show YYYY-MM-DD HH:MM:SS.MS

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

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

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

datetime(tonumber(left({PMovements.PM_Time},4)),
tonumber(mid({PMovements.PM_Time},6,2)), +
tonumber(mid({PMovements.PM_Time},9,2)), +
tonumber(mid({PMovements.PM_Time},12,2)), +
tonumber(mid({PMovements.PM_Time},15,2)),+
tonumber(mid({PMovements.PM_Time},18,2)))+
//tonumber(mid({PMovements.PM_Time},21,3)))

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.

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

@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