Solved

All Data created in the last 15 min

Posted on 2011-02-14
11
361 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Report Date Range Parameters 2 75
Visual Studio .Net 2012 and Crystal Reports 9 96
Sum of multiple formulas 6 23
division by zero in crystal reports 1 16
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 …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

820 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