INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Difference between two different date/time combinations

Difference between two different date/time combinations

(OP)
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

RE: Difference between two different date/time combinations

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

RE: Difference between two different date/time combinations

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Difference between two different date/time combinations

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:

CODE

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.

RE: Difference between two different date/time combinations

(OP)
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

RE: Difference between two different date/time combinations

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Difference between two different date/time combinations

(OP)
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

RE: Difference between two different date/time combinations

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Difference between two different date/time combinations

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

CODE

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

CODE

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

CODE

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

RE: Difference between two different date/time combinations

(OP)
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

RE: Difference between two different date/time combinations

Glad I could help.

Pete

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close