String Conversion 1

[moonknightt]

IS there a way to convert this Varchar to a date time
2796646:17:25:12 08-06-2005

 

[rosemaryl]

Dear moonknightt,

There is probably a more elegant way... but this gets the job done:

stringvar t := '2796646:17:25:12 08-06-2005'; //replace with your field

stringvar t := Mid(t,instr(t,':',1)+1, length(t));

stringvar array t2 := split(t, ' ');

cdatetime(t2[2] & ' ' & t2[1])

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[moonknightt]

thanks Rosemary,

But when I run it I get a bad datetime string format

 

[moonknightt]

bad date-time format string

 

[Turkbear]

Hi,
Just to validate Rosemary's code,that formula works fine in my CR10:
Resolves to ( Straight cut and paste, using her values):
8/6/2005 5:25:12PM





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rosemaryl]

Dear Moonknight,

I wrote it in CR10 which apparently has more options for instr so I didn't pick up a typo, so lets fix that first:

Let's try this:


stringvar t := '2796646:17:25:12 08-06-2005'; //replace with your field

stringvar t := Mid(t,instr(t,':')+1, length(t));

stringvar array t2 := split(t, ' ');

if length(t2[2]) = 10 and ubound(split(t2[1],':')) = 3
then
(If isdate(t2[2]) and istime(t2[1]) then
cdatetime(t2[2] & ' ' & t2[1])
else
cdate(0,0,0)
)

It would seem to me then that your field does not always have a date and time in it ... is that correct?

So we need to check for that which the above is now doing, however, it just checks for a valid date and time field, which I may not be accurate.


If the above isn't working, give me some of the formats in the field when you just place it on the report ... we need a wide variety to test for!


regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[moonknightt]

It works!! thank you very much

I want to use it in the record selection where the result is = to a date range

My date range formula is
"Report Date : " + ToText (Minimum ({?DateRange}))
+ " To " + ToText (Maximum ({?DateRange}))
and I wanta date selected based on this conversion

e.g in the record selection I insert
@conversion = @daterange
It returns no data

 

[rosemaryl]

Dear Moonknight,

The formula you are showing is a display formula to show the date range selected by the formula on a Report. Nicely formatted.

The report is going to be dog slow ... this conversion to a real date time field would be better performed at the database level. What database is this on?

Your selection criteria formula, however, would be properly formatted this way:

{@tester1} in {?DateRange}

Just to reiterate, this will be very slow because it cannot be passed to the where clause. So Crystal will return all records that match any other criteria (if any) and will then select records that match itself. Think of it this way: If your table has 100,000 records and you have no other criteria to limit the data being returned other then this formula in the daterange parameter, then Crystal will return all 100,000 records after which it will compare record by record to see if the criteria is met.

This is not optimal and I suggest finding a better solution.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[moonknightt]

Thanks a lot Rosemary for all your help.

We are working of an Oracle database and in some report instances it might be slow. but we cannot change the conversion on the database side because it is a world wide organization in every country. some countries operate on different time tables.

 

[rosemaryl]

Dear Moonknightt,

Well, you could certainly have them create a User Defined Function that does the conversion for you. You can call user defined functions and stored procedures in a SQL Expression. This would allow you to use the SQL Expression in the selection criteria.

Since this would execute on the database, it would allow you to select only those records that you need, taking a load off of the database. Something that would make your dba smile.

Best regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762