Database retrieving records based on time stamp

[Ranjeet_Singh]

Hello all,

                       I just  want to know that SQL query to retrieve specific time stamp record from the database using SQL server will work when same query is executed using SQL execute query.vi in labview.

                      Its not working actually.

My query is,

SELECT * FROM table name where col_name between '
date/time
2012-12-13 10:48:00' and '
date/time
2012-12-13 10:55:00'

I am using mdb file. This date/time 2012-12-13 10:48:00 is first time stamp to be searched.. This is string comes from DB Tools format datetime string.vi 

[PhillipBrooks]

A MS-Access query of a datetime field needs to be delimited wth the # (number sign / hash mark)

Something like:

SELECT * FROM table name where col_name between 
#
2012-12-13 10:48:00# and #2012-12-13 10:55:00#

I've never used the between condition; I don't know if it will work reliably with Access. I would normally use  >= and <= comparisons

Important: the # is specific to Access. We converted from Access to SQL Server in the last year and had to find all the places that used the "#' in our LabVIEW code and replace with a single quote.

[Ranjeet_Singh]

HI PhillipBrooks,

                                      Still not working. I am able to retrieve specific time stamp record from the database using little complex method. Just got an idea that it can be done by executing simpler way,

I though of retrieving using SQL query. 

Any other idea ?

[mikeporter]

What is the datatype of the column? Are you using SQL Server? I thought you said you were, but the previous answer you got gave the syntax for a JET (MS Access) database.

Finally, when you do a simple query of that column, how is the date formatted?

Mike...

[Ranjeet_Singh]

No i am trying to retrieve through labview only. for crosscheck i used SQL server. I am using JET only. My time column is of date/time type with format is '13-12-2012 10:48:00'. i verfied this 

[mikeporter]

So, the query Phillip posted should have worked. What was the result when you ran it from within LV?

Mike...

[PhillipBrooks]

I may be wrong, but I thought the datetime string when using Access was locale specific. If you are using US, then the date portion should be mm/dd/yyyy instead of yyyy-mm-dd.

You could use the %c format specifier to get the localized format and then wrap that with the # marks.

[Ranjeet_Singh]

HI mikeporter,

                                   I am getting error -2147217913 states Data type mismatch in criteria expression.

[Ranjeet_Singh]

For the cross check i used same command for MDF file, My query is

SELECT * FROM Data_120 WHERE Time_Stamp BETWEEN '2012-07-07 15:00:00.000' and '2012-07-07 15:14:00.000'

This query works fine when i am executing same query in SQL server but when i executes same commad using labview it says 'Invalid object name'

God knows what is happening.

[Ranjeet_Singh]

Hello all,