Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Query to retrieve records that had changed in last 24 hours

Posted on 2013-11-13
12
Medium Priority
?
6,196 Views
Last Modified: 2013-11-13
Hello,

I have a Microsoft SQL 2012 database that contains records of registration data that I have a big pull that extracts out all information and it works well. But the question I have is, how could I modify it to be able to query a timestamp on the record to only retrieve the records that had changed in the past 24 hours? Thats all that I am being asked for.

I created the view.export that is the collection of all the fields I want including the modified date, but I am not sure the syntax to only pull the past 24 hours. So if it doesnt have anything added in 24 hours, I get nothing back. If for instance my DB name is MyAwesomeDB and my view name is vwe.bestviewever how could I do this?

By the way, I am doing this from Excel as those are the types of reports that I am being asked for.  

Thanks
0
Comment
Question by:adembo
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 2

Author Comment

by:adembo
ID: 39645595
Sure, but I put it in Access as a query for SQL should be pretty much the same as Access, wouldn't it?
0
 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 668 total points
ID: 39645596
You would probably add something like this in your WHERE section:

AND  YoutTimestampField >= GETDATE() - 1

The "- 1" subtracts a day. There are other ways to do it, if you prefer a little readability:

AND  YoutTimestampField >= DATEADD(day, -1, GETDATE())

If you like seeing exact hours:

AND  YoutTimestampField >= DATEADD(hour, -24, GETDATE())
0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 664 total points
ID: 39645597
The SQL query would look something like...

SELECT ...
FROM myTable
WHERE myDateColumn > DATEADD(DAY, -1, GETDATE())
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 61

Expert Comment

by:mbizup
ID: 39645615
I'll leave Access as is, but add Excel...

<< Access as a query for SQL should be pretty much the same as Access, wouldn't it >>

Depends on where you are running the query from.  Some Access responses will not work.

For example, this is what I would suggest as an Access query - The syntax is a bit different, and it might not fly in your environment:

SELECT *
FROM YourTable
WHERE YourTimeStamp >=  DateAdd ("d" , -1, Date())
0
 
LVL 2

Author Comment

by:adembo
ID: 39645629
OK, my apologies. I made some assumptions as I'm fairly new to the database world.

Thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39645647
No worries -- Give the query in my last post a try too.  It *might* work, but I'm not sure.  I just wanted to make sure that your question was seen by people who are more familiar with querying SQL Server from Excel.
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39645654
If your database is SQL Server, my examples above will work.
0
 
LVL 12

Accepted Solution

by:
Tony303 earned 668 total points
ID: 39645725
SELECT *
FROM vwe.bestviewever
WHERE yourdatefield > DATEADD(Hour,-24,GETDATE())
0
 
LVL 2

Author Comment

by:adembo
ID: 39645736
Thanks All, Im trying out the queries and will respond shortly.
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 39645791
There is only one requirement assumed by the above - that you have a datetime field that is updated every time an edit is made.  If this is not true, then you will need to add such a field to the table, and be certain that it is updated with the current date and time whenever the record is updated (and, possibly when the record is added).
0
 
LVL 2

Author Comment

by:adembo
ID: 39645828
Great point bhess1, and fortunately there is a modified date field already there.

I was able to pull the data with most of what was offered here. Since they all were correct, I can split up the points.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

580 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