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 ?
John-S PretoriusTechnical Service Manager, Mid-AtlanticAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
0
mlmccCommented:
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
0
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor 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.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor 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.
0
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.