Using dates and time in datatable.select

Jan 11 2005 10:08 PM
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

Answers (2)