Link to home
Start Free TrialLog in
Avatar of curiouswebster
curiouswebsterFlag for United States of America

asked on

Need to tweak my DataGridView's query's DateTime field

My query feeds into a DataGridView which is why I rename the columns:

string query = "SELECT DriverLabel AS Driver, ReceivedDateTime AS MessageTime, FromPhone AS Phone, OriginalTextMessage AS Message FROM TextMessageLog ORDER BY ID DESC";

1) But I'd like to format the ReceivedDateTime to only show the time, and truncate the date.
2) I'd like to filter the query results for today only.

So I am a bit stuck on these two issues. Please let me know if you have ideas how to fix them.

Thanks,
newbieweb
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

try this

string query = "SELECT DriverLabel AS Driver, convert(varchar(max),ReceivedDateTime,108) AS MessageTime, FromPhone AS Phone, OriginalTextMessage AS Message FROM TextMessageLog where datediff(Day,getdate(),ReceivedDateTime)=0 ORDER BY ID DESC";

Open in new window

Avatar of curiouswebster

ASKER

I use Access. Your command throws an exception.

The database field is actually a text file containing the date and time.
try this for access:

string query = "SELECT DriverLabel AS Driver, Format(ReceivedDateTime, 'hh:mm:ss') AS MessageTime, FromPhone AS Phone, OriginalTextMessage AS Message FROM TextMessageLog where ReceivedDateTime=Date() ORDER BY ID DESC";

Open in new window

> The database field is actually a text file containing the date and time.

should be:

The database field is actually a text FIELD containing the date and time data.
did you tried my last post?
Great! That work to display only the time.

Any idea how to define a WHERE command for the query to retrieve only records where ReceivedDateTime is today?
I bring the condition you want in the query:
where ReceivedDateTime=Date()
I mean this section:
string query = "SELECT DriverLabel AS Driver, Format(ReceivedDateTime, 'hh:mm:ss') AS MessageTime, FromPhone AS Phone, OriginalTextMessage AS Message FROM TextMessageLog where ReceivedDateTime=Date() ORDER BY ID DESC";

if this is not what you want, explain more
Yes, but ReceivedDateTime is a text value:

1/10/2010 15:48:10

I just don't know how to parse a string in SQL

> Yes, but ReceivedDateTime is a text value:

should be

Yes, but ReceivedDateTime is a text value in the database:
so try this where clause:

where Format(ReceivedDateTime, 'mm:dd:yy')=Format(Date(),'mm:dd:yy')

Open in new window

if previous code doesn't work, try this one:

where Format(ReceivedDateTime, 'mmm:dd:yyyy')=Format(Date(),'mmm:dd:yyyy')

Open in new window

Both throw an exception:

ex = {"Syntax error (missing operator) in query expression 'where Format(ReceivedDateTime, 'mm:dd:yy')=Format(Date(),'mm:dd:yy')'."}
ex = {"Syntax error (missing operator) in query expression 'where Format(ReceivedDateTime, 'mmm:dd:yy')=Format(Date(),'mmm:dd:yy')'."}
I found I had the word "where" in twice. When I took that out, I got:

ex = {"No value given for one or more required parameters."}
could you upload sample access db here?
and if you can attach your last executed query here
string query = "SELECT DriverLabel AS Driver, Format(ReceivedDateTime, 'hh:mm:ss') AS MessageTime, FromPhone AS Phone, ";
          query += "OriginalTextMessage AS Message FROM TextMessageLog where Format(MessageTime, 'mmm:dd:yy')=Format(Date(),'mmm:dd:yy') ORDER BY ID DESC";
ASKER CERTIFIED SOLUTION
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK. There are no longer any exceptions. Great!

But the WHERE claue does not seem to work. One record has the following value for ReceivedDateTime:

2/14/2010 14:44:06

When I run the program without the WHERE clause, the DataGridView has 615 records, the same number of records in the database. So I tell you this since I confirm that withouth the WHERE clause, the query is correctly populating the DataGridView. But when using the WHERE clause, no records are displayed.
My Bad!

I was updating the wrong database. Everything works grerat now.

Thanks!
newbieweb
Glad to help,
Regards,