TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
andrew.dunningham
NA
1
0
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
Reply
Answers (
2
)
How to display the record and use NEXT and Prev buttons?
Managing SQL Server using C#