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

John-S Pretorius
John-S Pretorius used Ask the Experts™
on
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 ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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 McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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 PretoriusSystems Engineer

Author

Commented:
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.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Learn More!
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
You could do that.

That was one of my thoughts.

However how about this

Create 2 formulas
MyDate
DateTime(Split(({PMovements.PM_Time},'.')[1])

Second one
Millisecs
Val(Split(({PMovements.PM_Time},'.')[2])

SOrt on the date field then the millisecs

mlmcc
John-S PretoriusSystems Engineer

Author

Commented:
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 PretoriusSystems Engineer

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial