Difference between two different date/time combinations 2

[RoseDCsyst]

Hi;
Can someone help me to write the following formula within Crystal Report. Here is the data I am working with and what I need:
There is a field called PSCrtDt (YYYYMMDD(number) and PSCrtTime (number) which I want to combine into one field called PSTiming. I then have a field called XHCrtDt (YYYYMMDD(number) and XHCrtTime (number) which I want to combine into one field called XHTiming. Then I want to know If PSTiming <= XHTiming then Yes else No

Appreciate any help I can get.

Thank you
Rose

 

[pmax9999]

In most databases, dates and times are stored with data types of Date and Time respectively but you seem to be suggesting that in your database they are being stored as numbers. Based on your post I am guessing that a date of 15 May 2016 would show as 20,160,515 but it isn't clear just how the time is being stored. Can you advise whether my assumption of date is correct and if not provide some sample data and how that data should be interpreted. Can you also provide sample data for the time field please, and how the sample times should be interpreted.

Once we understand the data it should be simple to achieve what you need.

Cheers
Pete

 

[SkipVought]

Are you absolutely certain that your yyyymmdd date fields are numbers and not numeric characters (text). I've worked with SAP tables with dates stored this way AS TEXT. That way, a specific date instance can contain no value (text) which is a whole lot different than if it were numeric there is really not a no value, as ZERO is a value, for instance.

So in order to calculate any date/time difference, you might need to categorize your date/time pairs, since those pairs missing either the subtrahend or minuend cannot be acted on, while those that can be subtracted, need to be converted to real date/time values before applying math. In my world, this was done by joining a Calendar table that had a yyyymmdd field associated with calendar date, manufacturing day, accounting day, YTD, MTD, etc.

BTW, after reading your question again, I see you only need a comparison. So each date and time text value would be concatenated to get a date/time text value while any date and time real values would be added to get a date/time real value to do math.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmax9999]

Actually, just picking up on Skip's point that you only need to compare date/times rather than calculating the difference, it isn't even necessary to combine the date and time components (it is only necessary to consider the time component if the dates are the same, otherwise the time can be ignored).

As long as the Dates are in the format of YYYYMMDD and the time fields are numeric (or if text are in the format HHMM or similar), the following formula should work:

If      {Table.PSCrtDT} > {Table.XHCrtDt}
Then    'No'
Else    
If      {Table.PSCrtDT} < {Table.XHCrtDt}
Then    'Yes'
Else
If      {Table.PSCrtTime} < {Table.XHCrtTime} 
Then    'Yes'
Else    'No'

Hope this helps.

 

[RoseDCsyst]

Hi pmax9999 and SkipVought:

First thank you both for responding. Excellent help and learned quite a few things.

pmax9999 I used your suggestion with a little modification for additional criteria and the formula now appears to be working. Never thought about only needing to test the time field if the date criteria was yes, learned something new and it helps with thinking through how to write criteria.

SkipVought and pman9999 I wanted to share with you what our file is showing for data and field definition so it will help me to better understand if the files are actually numeric:

field definition on the file is PScrtDt (Date Created):Number data displays as 20.160.620.00 and PSTimeCrt (Time Created): Number data shows as 145.915.00

I really appreciate the help.

Rose

 

[SkipVought]

Number data displays as 20.160.620.00 and PSTimeCrt (Time Created): Number data shows as 145.915.00

Sorry. You do not have 'number' data. Numbers are values that you can do math with. You cannot do math with your 'numbers'!

What you have is TEXT. 20160620 as a date representation, is not a value with which you can do any meaningful math. And I have no idea why your date would be represented as 20.160.620.00. But it surely cannot be used in any mathematical computation. Real dates, however, can and are used in math.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RoseDCsyst]

Hi SkipVought

Thank you.

Would I be able to convert the text to a number if I had to with the data the way it is?

Thanks
Rose

 

[SkipVought]

First, I wanted to dispel the misconception that what you referred to as a number was not a number, albeit composed of numeric characters, yet not fit for mathematical calculations.

To convert ANY text representation of a date to a real date, you would need to parse appropriate portions and map those parsed portions representing year, month and day to a DateSerial function that would return a date serial value.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmax9999]

While I accept that storing the date as a number in this format is not the way it is usually done, I am less convinced that your assertion is incorrect (frankly, I have seen developers do sillier things than this).

A simple test would be to try a mathematical operation on the date and time fields (eg, {Table.PSCrtDT}/50). If it accepts the formula and performs the operation, the field is a number.

Assuming the two fields are, in fact, numbers (as opposed to numeric text as suggested by Skip), the following conversion formulas will work:

1. Convert Date Field to True Date

DATE(VAL(Left(ToText({Table.PSCrtDT}, '#'),4)),VAL(Mid(ToText({Table.PSCrtDT}, '#'),5,2)),VAL(Right(ToText({Table.PSCrtDT}, '#'),2)))

2. Convert Time Field to True Time

TIME(VAL(Left(Right('0' + ToText({Table.PSCrtTime},'#'),4),2)),VAL(Mid(Right('0' + ToText({Table.PSCrtTime},'#'),4),3)),0)

3. Combine Date and Time fields to True Date/Time

DATETIME(VAL(Left(ToText({Table.PSCrtDT}, '#'),4)),VAL(Mid(ToText({Table.PSCrtDT}, '#'),5,2)),VAL(Right(ToText({Table.PSCrtDT}, '#'),2)),VAL(Left(Right('0' + ToText({Table.PSCrtTime},'#'),4),2)),VAL(Mid(Right('0' + ToText({Table.PSCrtTime},'#'),4),3)),0)

Hope this helps.

Cheers
Pete

 

[RoseDCsyst]

Hi pmax9999;

I did exactly what you suggested and enter a formula (just to see what would happen) which was {XHCTRL00.XHCNTD}/50 and first crystal allowed me to save the formula and when I added this formula field to the report it actually showed results. So I guess this really is a numeric field with a odd set up. Thank you for suggesting the test and also thank you very much for the conversions as these will come in very handy going forward.

The report I was attempting to create looks great now and is providing all the information we need.

Thank you
Rose

 

[pmax9999]

Glad I could help.

Pete