Avatar of John-S Pretorius
John-S Pretorius
Flag 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 ?
Crystal Reports

Avatar of undefined
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 :-

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Mike McCracken

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John-S Pretorius

ASKER
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