Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

All Data created in the last 15 min

I am looking to filter data by time added.  I have a field "somast.addtime" that will give me the time entered but I am not sure how to filter it as I would a date.   Looking for all data in the last 15 minutes at any given time.
0
jasonspopma
Asked:
jasonspopma
  • 6
  • 4
1 Solution
 
mlmccCommented:
Is the field just a time field or is it a datetime?

Basic filter would be

DAteDiff('n', {YourDateTimeField}, CurrentDateTime) <= 15

If your field is a string represnting the time then you would have to convert it to time first

mlmcc
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
If somast.addtime is a datetime field, you could use the following in your record selection criteria:

{somast.addtime} in DateAdd ("n",-15 ,CurrentDatetime ) to CurrentDateTime

~Kurt
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
FYI - the datediff won't pass to the database for processing because basically create a runtime formula against the actual database field.  The DateAdd passes because it simply compares the database fields to constant values.

~Kurt
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
jasonspopmaAuthor Commented:
Add Date "somast.adddate" is a different field then Add Time "somast.addtime".

Is this still correct? {somast.addtime} in DateAdd ("n",-15 ,CurrentDatetime ) to CurrentDateTime
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
What's the actual format of  your addtime field? Time, Number or String?

~Kurt
0
 
jasonspopmaAuthor Commented:
string
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
What's the exact format of the string?  For example, is it '00:00:00', '000000' or are there even leading zeroes?  Do specific places represent hours, minutes and seconds?  We can't really provide a formula until we know what the data looks like.

~Kurt
0
 
jasonspopmaAuthor Commented:
00:00:00
hour - min - sec
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
What database are you using?  SQL Server, Oracle, etc..?  Also, what type of datasource are you using?  Straight tables, a view, a SQL Command, etc.?

~Kurt
0
 
jasonspopmaAuthor Commented:
SQL - Straight Tables
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Okay - assuming adddate is an actual date field, you'll need to concatenate the date and time values into a date time string, then convert it to a date time value.  You could do this in a formula, but it would never be passed to the database for processing, which would be a bad thing.  Instead, because you're using SQL Server, I'd recommend creating a SQL Expression field (you'll see it in your Field Explorer) with syntax like the following:

CAST(CONVERT(VARCHAR(20),"somast"."adddate",101)  + ' ' + "somast"."addtime" AS DATETIME)

The output will be a datetime field the combines both the basic date value from adddate and the time  from  addtime.  Once you have this, you can then use this in your record selection criteria as follows:

//{%date_time} is the name of the SQL expression field you just created
{%date_time} in DateAdd ("n",-15 ,CurrentDatetime ) to CurrentDateTime

~Kurt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now