[Webinar] Streamline your web hosting managementRegister Today

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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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