Need help with cdatetime formula & datediff formula

[Former Member]

I need some help writing formulas:

1) I have

a) Evaluating date 10/21/09

b) Evaluating time 03:18PM

c) Monitoring date 10/21/09

d) Monitoring time 03:21PM

How would I write my formula using cdatetime to combine evaluating date plus evaluating time? (e)

I would also neeed to write a formula to combine monitoring date plus monitoring time as well. (f)

2) I would also need to write a formula to get the number of hours different between (evaluating date + evaluating time) and

(monitoring date + monitoring time) using datediff?

Thanks in advance for your help.

T

2)

[Former Member · Accepted solution]

This should do the trick for you...

EvalDateTime
DateTime(Date({TableName.EvaluatingDate}),Time({TableName.EvaluatingTime}))

MonitorDateTime
DateTime(Date({TableName.MonitoringDate}),Time({TableName.MonitoringTime}))

HoursDiff
DateDiff("h", {@EvalDateTime},{@MonitorDateTime})

HTH,

Jason

[Former Member · Accepted solution]

I put the first statement in, and it gave me this error message:

"A date-time is required here!"

Do we need another date after datetime?

[Former Member · Accepted solution]

I used the exact values you provided in the original post. What are the data types of the date values and the time values?

[Former Member · Accepted solution]

Try it this way

DateTime ( {TableName.MonitoringDate } , { TableName.MonitoringTime } )

I added some spaces to make it easier to read.

[Former Member · Accepted solution]

The data values are exactly the same way as in my original post.

[Former Member · Accepted solution]

Trang

I don't have any databases that have separate date & time data types. Odds are you'll have to play around with CR's date & time formulas formulas until you can get CR to recognize the fields correctly. Once CR sees the dates as dates and the times as times, DateTime & DateDiff functions will work.

Jason

[Former Member · Accepted solution]

Jason,

The first part works, but now I can't seem to figure out the datediff between the two dates. I queried off the dates alone, and there are some blank dates in there.

How would else should I write the datediff statement better to cover for the blank dates?

Please help!

Thanks,

Trang

[Former Member · Accepted solution]

Well you have a couple of options...

1) You can calculate a a date using the "CurrentDateTime"

2) You could calculate a default date using the the "Evaluating date"

3) Set it to "0"

1)

IF IsNull({MonitorDate}) THEN CurrentDateTime ELSE {MonitorDate}

2) this example adds a default 24hrs to the eval datetime if the monitor dt is null

IF IsNull({MonitorDate}) THEN DateAdd("h", 24, {EvalDate}) ELSE {MonitorDate}

3)

IF IsNull({MonitorDate}) THEN {EvalDate} ELSE {MonitorDate}

Basically, it just depends on what you want the result to be...

HTH,

Jason