SQL Query to retrieve records that had changed in last 24 hours
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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())
<< 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())
ASKER
OK, my apologies. I made some assumptions as I'm fairly new to the database world.
Thanks
Thanks
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.
If your database is SQL Server, my examples above will work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks All, Im trying out the queries and will respond shortly.
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).
ASKER
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.
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.
ASKER