how to extract hourly data

[user_1]

Hi

I am developing an application using labview and MSAccess.

The database contains records that is generated many times an hour.

For a particular hour there may be more than one records.

I have to extract the records generated in a particular hour.What is the efficient method for this?

I have generated records of particular year,particular month and particular day by using SQL query(using Yea(...),Month(...),day between...)

But i dont know the SQL query to extract hourly data.Is there any way for that?

Thanks...

[Robst]

Hello:

Lets suppose that the table you use to collect data is called "Datalog" and that the field where you store the date is called "Date". And SQL Statement that may work for you is the next:

SELECT * FROM Datalog WHERE Date >#2007/04/09 09:00:00# AND Date <#2007/04/09 10:00:00#;

This statement will return the records written between 9 a.m. and 10 a.m. of today.

Hope this helps, and let me know how it works.

Robst.

[user_1]

Thank you so much for your reply sir.

I am giving a sample date format.My table say datalog with field date contain date in the format 30/09/2005 01:26:00 AM

I tried with your query sir.But i am not sure it will work with this format.So can you please help me?

I am using a time stamp control in the fron panel through which i can enter year month date and time DD/MM/YY.So far i have kept the time unused.

And in coding,i was using  a 'format date/time' control and i have set the formatto %m%d%y so i can read year or month from the database by takingyear() or month().

But if i have to find hourly data i can use the time field also.

But i am not sure how to format the date using format date/time control so i can include the time also as

in the database format(say 30/09/2005 01:26:00 AM).

Can u help me regarding this?or is there any other feasible method?

[Dennis_Knutson]

Not sure why you are formatting the date as mm/dd/yyyy when the database is dd/mm/yyyy but adding the time is just a matter of specifying the format. The on-line help for the function explains that. If you need a picture, here it is.

p.s. It's often a good idea to debug your queries in Access and then turn on SQL View to see what you need for the LabVIEW database toolkit.

Message Edited by Dennis Knutson on 04-09-2007 09:45 PM

[Robst]

Hello All:

As a comment, in my experience working with MS Access, when specifying the date format , the only one that works for me ALWAYS is the YYYY/MM/DD (YYYY/MM/DD HH:MM:SS in case your need also hours, minutes and seconds) format. The other ones may work but sometimes they offer unpredictable results.  I confirmed this in some DB forums but I couldn´t find the URL

The format specified on the query must not be the same that is shown in Access, since it is only a way to represent the data stored.

And as Dennis said, it is always a good idea to check your querys in Access. To do so, right click on the query dialog and select "SQL View" from the popup menu. There you can define your query as a SQL Statement and test it.

Robst.

[user_1]

Ok,thank you.I think this one will help me.

Thanks for the replies