curiouswebster

asked on 2/14/2010

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

Last Comment

Reza Rad

8/22/2022 - Mon

Reza Rad

2/14/2010

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

2/14/2010

ASKER

I use Access. Your command throws an exception.

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

Reza Rad

2/14/2010

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

2/14/2010

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

2/14/2010

did you tried my last post?

curiouswebster

2/14/2010

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

2/14/2010

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

Reza Rad

2/14/2010

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

2/14/2010

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

2/14/2010

ASKER

> Yes, but ReceivedDateTime is a text value:

should be

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

Reza Rad

2/14/2010

so try this where clause:

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

Open in new window

Reza Rad

2/14/2010

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

2/14/2010

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

2/14/2010

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

2/14/2010

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

2/14/2010

and if you can attach your last executed query here

curiouswebster

2/14/2010

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

2/14/2010

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

2/14/2010

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

2/14/2010

ASKER

My Bad!

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

Thanks!
newbieweb

Reza Rad

2/14/2010

Glad to help,
Regards,