Avatar of curiouswebster
curiouswebster
Flag 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
.NET ProgrammingMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Reza Rad

8/22/2022 - Mon
Reza Rad

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

curiouswebster

ASKER
I use Access. Your command throws an exception.

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

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
curiouswebster

ASKER
> 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.
Reza Rad

did you tried my last post?
curiouswebster

ASKER
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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Reza Rad

I bring the condition you want in the query:
where ReceivedDateTime=Date()
Reza Rad

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
curiouswebster

ASKER
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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
curiouswebster

ASKER
> Yes, but ReceivedDateTime is a text value:

should be

Yes, but ReceivedDateTime is a text value in the database:
Reza Rad

so try this where clause:

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

Open in new window

Reza Rad

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

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

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
curiouswebster

ASKER
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')'."}
curiouswebster

ASKER
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."}
Reza Rad

could you upload sample access db here?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Reza Rad

and if you can attach your last executed query here
curiouswebster

ASKER
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
Reza Rad

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
curiouswebster

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
curiouswebster

ASKER
My Bad!

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

Thanks!
newbieweb
Reza Rad

Glad to help,
Regards,