3 Replies - 9148 Views - Last Post: 29 April 2013 - 06:19 AM Rate Topic: -----

#1 Keylogger  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 344
  • Joined: 14-February 11

[Solved] DataTable.Select only Date from DateTime

Posted 29 April 2013 - 05:06 AM

Hi guys,

I'm trying to select only the Date from DateTime, but without success. At first I thought it was a Access Problem, but then I tested directly on SQL Access and it did what I needed.

Input: 29-04-2013 12:00:00
Output (what I need): 29-04-2013
table.Select(String.Format("OpenedDate = {0}", dateTimePicker1.Value.ToShortDateString()), "ID DESC");

So, obviously the field "OpenedDate" comes with: 29-04-2013 12:00:00
In Access we can do something like "LEFT(OpenedDate, 10)" and it will retrieve 29-04-2013. But doesn't work on DataTable.

Also I would also like to use "LIKE query".
How can I do it? Thanks.

This post has been edited by Keylogger: 29 April 2013 - 06:20 AM


Is This A Good Question/Topic? 0
  • +

Replies To: [Solved] DataTable.Select only Date from DateTime

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3824
  • View blog
  • Posts: 13,556
  • Joined: 12-December 12

Re: [Solved] DataTable.Select only Date from DateTime

Posted 29 April 2013 - 05:27 AM

If OpenedDate is stored as a string then the expression won't work because the string values needs to be enclosed in apostrophes, and the values won't be equal because of the time element. Try

table.Select(String.Format("OpenedDate LIKE '{0}*'", dateTimePicker1.Value.ToShortDateString()), "ID DESC");


or
table.Select(String.Format("Left(OpenedDate, 10) = '{0}'", dateTimePicker1.Value.ToShortDateString()), "ID DESC");

You are also assuming that the short-date format will be dd/mm/yyyy, rather than mm/dd/yyyy (or something else). Print out a date in short-date format to confirm that it is in the format that you need.
Was This Post Helpful? 0
  • +
  • -

#3 Keylogger  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 344
  • Joined: 14-February 11

Re: [Solved] DataTable.Select only Date from DateTime

Posted 29 April 2013 - 06:10 AM

Hi, first of all thanks.

The "openedDate" is shown in the Datagridview, and it's DateTime, so it shows: 29-04-2013 12:00:00.
This field, is a DateTime field, not a string a field. The problem is, I want to compare only the 'dd/mm/yyyy' and not 'dd/mm/yyyy hh:mm:ss'.

My only problem is getting only the day,month and year from that DateTime. I've searched alot..and it seems DataTable has few functions for DateTimes.

With this:
table.Select(String.Format("OpenedDate LIKE '{0}*'", dateTimePicker1.Value.ToShortDateString()), "ID DESC");

It retrieves me an error:

Quote

Cannot perform 'Like' operation on System.DateTime and System.String.

And Ok, I understand that. But, again, I need only the Date from the DateTime, if I can get it..I think my problem will be solved.
Was This Post Helpful? 0
  • +
  • -

#4 Keylogger  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 344
  • Joined: 14-February 11

Re: [Solved] DataTable.Select only Date from DateTime

Posted 29 April 2013 - 06:19 AM

Just solved it. I tried this solutions hours ago, but with different info. So, I convert the Date to String, so I can compare with Like.
String.Format("convert(OpenedDate, 'System.String') LIKE '%{0}%'", textBox1.Text), "ID DESC")

Thanks!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1