Finding Ranges in DATETIME fields

[dlongnecker]

I've been given the task of taking one of our existing bases and pulling querying information with a parameter on a DATETIME field.

The situation is as:
If a user performs an action in {?timerange}, then count (or limit the provided results in the report).

I want my end user to be able to select if a work order was closed in 10, 15, 30, 45, 60, 90, and 120 minutes. Our base stores the information in the following format:

DateTime (2001, 12, 07,13, 01, 27)

Roughly, and idealistically, I would like to be able to do something like:

{TASKS.CLSDBY} = 'USER' and {TASKS.OPENBY} = 'USER' and
TimeValue({TASKS.CLSDDATE}) <= (TimeValue({TASKS.OPENDATE}) + {?timeclosed})

... and I'm doing a count off of that field. It's messy, but it seems to be working until I find a better way.

With the dates being stored in that delimited format, I'm unable to see what field to change for the time frame and how to deal when it changes hours/days and changes fields.

Any ideas would be great!

Thanks!

-David

David R. Longnecker
Management Information Services
Wichita Public Schools, USD 259
dlongnecker@usd259.net

 

[synapsevampire]

Timevalue will get you in trouble if midnight is involved.

{TASKS.CLSDBY} = 'USER' and {TASKS.OPENBY} = 'USER' and
TimeValue({TASKS.CLSDDATE}) <= (TimeValue({TASKS.OPENDATE}) + {?timeclosed})

might be:

{TASKS.CLSDBY} = 'USER'
and
{TASKS.OPENBY} = 'USER'
and
DateDiff (&quot;n&quot;, {TASKS.OPENDATE}, {TASKS.CLSDDATE}) <= {?timeclosed}

Where the parameter timeclosed is the minutes (10, 15, 30, 45, 60, 90, and 120) specified by the user.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dlongnecker]

Now, a question: Can I use minutes or do I need to do it in seconds?

i.e. value = 900 = 15 minutes.

-David

 

[synapsevampire]

Preload your parameter with choices (to simplify the user input, they can also enter a different value) for the various minute increments, this formula allows for minutes.

datediff can show the difference between any 2 datetimes for:

Interval type value
Description
yyyy
Year

q
Quarter

m
Month

y
Day of year

d
Day (both &quot;y&quot; and &quot;d&quot; find the difference in days)

w
Number of weeks between startDateTime and endDateTime

ww
Number of firstDayOfWeek's between startDateTime and endDateTime

h
Hour

n
Minute

s
Second

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

To supplement Synapsevampire's last post, all of these arguments are valid for the Dateadd() function as well.

You can add years, months, weeks, weekdays, minutes and seconds using the same arguments. You can also subtract, just make the number argument a negative number. Dateadd() is a great function, check out the help screens on this and I am sure you will appreciate this. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[dlongnecker]

Great! Thank you both!

-David