Solved

All Data created in the last 15 min

Posted on 2011-02-14
11
359 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
  • 6
  • 4
11 Comments
 
LVL 100

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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 …
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

929 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now