Using dates and time in datatable.select
Hi,
I am having trouble selecting a set of records from a datatable where they are restricted by time.
One of the tables looks like this (MS Sql server):
Temperature
Met_stn_id (char(6)), tempdate, Tempdate_10m (datetime), TempC
To Note.
Dates are stored in New Zealand format - dd/mm/yy hh:mm:ss
Some data (simplified, tempdate not shown)
Temperature
Met_stn_id Tempdate_10m tempC
tes 17/06/2002 17:00:00 11.77
test 17/06/2002 17:10:00 11.77
test 17/06/2002 17:20:00 12.16
test 17/06/2002 17:40:00 12.16
The datatable.select command is:
sqlstring = "(Met_Stn_id = '" & met_stn_id & "') and Tempdate_10m = #" & strthedate & "#"
giving a value of "(Met_Stn_id = 'TEST ') and Tempdate_10m = #06/17/2002 17:10:00#"
aTemperatureRow = dtTemperature.Select(sqlstring)
THIS RETURNS A NULL SET OF ROWS
But If I execute the query in SQL Query Analyser , it results in a recordset of 1 record
If I change the query to test for >= then the correct number of records are returned. ie
"(Met_Stn_id = 'TEST ') and Tempdate_10m >= #06/17/2002 17:10:00#"
***
Other points:
using the date in 17/06/2002 format results in an error of unrecoginsable date
using single quotes instead of # results in incorrect comparison
using the construction found in examples: tempdate_10m = " & thedate fails as the date field contains a space at the start of the time portion of the date.
Some other questions:
In Sql I have to use the sql Convert function, can I use it here?
Is the Datatable.select a sql select statement or someother format, (ie can I use sql functions such as sum etc in the datatable.select staetment?
____
The aim, by the way, is to colapse the 3 met tables (temp, rainfall and light) into one table for every 10mins, with all data in one tuple.
Thanks
Andy