convert date/time to string

[farrelm2]

I need to report on a date/time field and use a formula to capture all instances from today for one week. I converted the date/time to string because I also needed to subtract 4 hours from the time because of time zone issues. I do not know how to do both. Any suggestions?

 

[synapsevampire]

Don't convert to a string to subtract or add to times, use:

datediff("h",-4,{MyTable.MyDateTime})

To get the past weeks data only, modify the record selection criteria, as in:

What does the following mean?:

"use a formula to capture all instances from today for one week"

Why do you need a formula? And is it truly from today for one week, or is it the past week?

You can limit rows in the report (and use this same logic as criteria for formulas if you really need to). Use Report->Edit Selection Formula->Record, and place something akin to one of the following in there:

For the next week:

{MyTable.MyDateTime} >= currentdate+7

For the past week:

{MyTable.MyDateTime} >= currentdate-7

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[farrelm2]

If I were to run the report today, I would need all instances from 10/4-10/10. It is a date field used for scheduling purposes.

Using the
{MyTable.MyDateTime} >= currentdate+7
did not give me the correct data, it only brought up the one instance that was scheduled past 10/10.

And how do I integrate both the datediff and currentdate formulas....in record selection?

 

[farrelm2]

Also, using
datediff("h",-4,{MyTable.MyDateTime})
gives me an error at '-4' saying a date is required here.

 

[synapsevampire]

Sorry, I'm tired from a long flight.:

{MyTable.MyDateTime} <= currentdate+7
and
{MyTable.MyDateTime} >= currentdate

datediff(&quot;h&quot;,{MyTable.MyDateTime},currentdate) >= -4
and
datediff(&quot;h&quot;,{MyTable.MyDateTime},currentdate) <= 0

You can also use the IN for each of these, but I tend to be explicit.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[farrelm2]

The date range now works correctly, but adding the datediff formula brings up no results. The following is my record selection formulas:

{cm3rm1.status} <> &quot;closed&quot; and
{cm3rm2.down_start} <= currentdate+7 and
{cm3rm2.down_start} >= currentdate and
datediff(&quot;h&quot;,{cm3rm2.down_start},currentdate) >= -4 and
datediff(&quot;h&quot;,{cm3rm2.down_start},currentdate) <= 0

What is wrong with this?

 

[Naith]

Do you mean:

{cm3rm1.status} <> &quot;closed&quot; and
DateDiff(&quot;h&quot;,{cm3rm2.down_start},CurrentDate) >= -172 and
DateDiff(&quot;h&quot;,{cm3rm2.down_start},CurrentDate) <= -4;

Naith

 

[Naith]

On second thought, I think that should be >= -164 and <= 4.

 

[farrelm2]

Naith,
Your formula works to capture all instances 7 days or less in advance, but the time that I need to report must READ 4 hours less than what the times read now. Any suggestions?

 

[Naith]

I thought that's what I just gave you.

There are 168 hours in a week. So, moving the window from 0 to 168 to 4 to -164 captures the same quantity of time with a -4 hour start and end date.

You aren't trying to combine this with the CurrentDate+7 clause, are you? You don't need that part.

Naith

 

[farrelm2]

I am not combining this with the currentdate+7 clause, but I understand how the 168 works. However, besides gathering all those instances, I need to subtract 4 hours from all those times. Currently an instance on my report reads 10/8/02 12:00 pm, but should read 10/8/02 8:00am, because of time zone issues.

 

[Naith]

Aren't you talking about two different formulas here?

It seems to me that your selection formula is:

{cm3rm1.status} <> &quot;closed&quot; and
DateDiff(&quot;h&quot;,{cm3rm2.down_start},CurrentDate) >= -164 and
DateDiff(&quot;h&quot;,{cm3rm2.down_start},CurrentDate) <= 4

but what you actually want to display is a completely separate formula of:

{cm3rm2.down_start}-0.166

Isn't that the case? Set me straight if I've misunderstood you, but if I'm understanding you correctly, altering the appearance of your data is not something you are going to accomplish in the record selection criteria.

Naith

 

[farrelm2]

Yes, I need to alter my data by 4 hours. Your datediff or the currentdate+7 formulas will work to capture the correct instances, but I need another formula to subtract 4 hours from my times. Where would this kind of formula go?

 

[synapsevampire]

You might consider a SQL Expression to return the date less 4 hours.

datediff(h,-4,{cm3rm2.down_start})

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

You create a new formula, and place something like:

{cm3rm2.down_start}-0.1666

in it. (0.1666 is about 4 hours, I think.) Place the formula in your report where you expect the date to be displayed, instead of your actual date field.

Naith

 

[farrelm2]

Thanks Naith! Problem solved!