Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

All Data created in the last 15 min

Posted on 2011-02-14
11
Medium Priority
?
366 Views
Last Modified: 2012-06-27
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
Comment
Question by:jasonspopma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 34893181
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34893224
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34893240
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
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 

Author Comment

by:jasonspopma
ID: 34896104
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34897155
What's the actual format of  your addtime field? Time, Number or String?

~Kurt
0
 

Author Comment

by:jasonspopma
ID: 34897166
string
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34897706
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
 

Author Comment

by:jasonspopma
ID: 34897733
00:00:00
hour - min - sec
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34897772
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
 

Author Comment

by:jasonspopma
ID: 34897862
SQL - Straight Tables
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 2000 total points
ID: 34898859
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question