• Welcome to SQLitening Support Forum.


Welcome to the SQLitening support forums!

Main Menu

Trouble with SQL Query

Started by Andrew Lindsay, June 30, 2009, 01:00:52 AM

Previous topic - Next topic

Andrew Lindsay

I am having trouble with the following Query.

  sqlQuery = "SELECT COUNT(*) As RowCount FROM Data Where RecordTime >= '2009/04/01T00:00:00' AND RecordTime < '2009/04/01T01:00:00'"
  Replace "'" With $Dq In sqlQuery
  NumberOfRows = Val(slFN("RowCount",1))

I should get an answer of 60 (I have data for every minute), however, I get an answer of 0.  Any assistance would be appreciated.



Andrew Lindsay


I stumbled across the answer to above, it is a force of habit to format dates with a '/' character, but it needs to be a '-' character...


I am about to head offshore tonight so I will get my question in now, because I know I am not going to be able to solve it myself.

I have two tables.

Table one contains the monitoring data at minute intervals.  The table looks like this.

Time    Hs        Vw        Tw   
2009-04-01 00:00:00    0.4        20        45   
2009-04-01 00:01:00    0.5        30        20   
2009-04-01 00:02:00    0.4        30        45   
...    ...        ...        ...   

Table two contains my reporting criteria

Start Timeend Time    Hs_crit        Vw_crit        Tw_crit   
2009-04-01 00:00:002009-04-01 01:00:00    0.5        30        40   
2009-04-01 01:00:002009-04-01 02:00:00    0.25        30        20   
2009-04-01 02:00:002009-04-01 03:00:00    0.3        30        30   

For each Criteria Row, I want to count the number of entries that exceed the criteria, i.e. the results would be something like...

Start TimeEnd TimeCount
2009-04-01 00:00:002009-04-01 01:00:0035
2009-04-01 01:00:002009-04-01 02:00:0060
2009-04-01 02:00:002009-04-01 03:00:000

Any assistance in this would be greatly appreciated.



Rolf Brandt

One or more things might cause the problem.

Why are you using Replace? What is the value of '$Dq' in your code?
Replace "'" With $Dq In sqlQuery

Since SQLite saves date and time anyway as a text string you should check how the data is saved. If your application wrote the data to the database then use the same format in your WHERE string. It should match the data.

Also when writing a DateTime value to the database it would be best to put the value in between quotes, like '2009-04-01 01:00:00'. This would make sure that SQLite does not interprete it as a numerical value.

So if you use the format "YYYY-MMYDD HH:MM:SS" then the WHERE part of you SQL string should look like this:
Where RecordTime >= '2009-04-01 00:00:00' AND RecordTime < '2009-04-01 01:00:00'

I like to cook with wine - sometimes I even add it to the food.

Bern Ertl

Hi Andrew, Rolf is correct, string values can be enclosed with single quotes in SQL statements.

Andrew Lindsay


Thanks guys.  I've got that one sorted out.  I thought that my problem was that I was using single quotes and not double quotes, however, the reason for my error was that I was using a date format of YYYY/MM/DD instead of YYYY-MM-DD.  i,e.

  sqlQuery = "SELECT COUNT(*) As RowCount FROM Data Where RecordTime >= '2009/04/01T00:00:00' AND RecordTime < '2009/04/01T01:00:00'"
This should read

  sqlQuery = "SELECT COUNT(*) As RowCount FROM Data Where RecordTime >= '2009-04-01T00:00:00' AND RecordTime < '2009-04-01T01:00:00'"

Then it all works out fine...

Now I have moved onto the second part of the question where I am trying to use the criteria from one table as a basis for a query on another table.