0

Why does C# still get the time from SQL if stored procedure only gets the date from a column:

CONVERT (DATE,RecordAdded, 103) as DateCaptured

Result:

Result in Listview

I am selecting the date from a datetime column in SQL, then dumping it into a Listview. But I only want the date to display in the listview.

ListViewItem FeedbackTable = new ListViewItem(FeedbackReader["DateCaptured"].ToString());
massko
  • 589
  • 1
  • 7
  • 22
ExpressDude
  • 45
  • 10

3 Answers3

0

You can use the .ToString() for formatting your DateTime as string. Watch this link for more information. In your case this should work:

ListViewItem FeedbackTable = new ListViewItem(FeedbackReader["DateCaptured"].ToString("yyyy-MM-dd"));

Notice you maybe need to convert Convert.ToDateTime(FeedbackReader["DateCaptured"]);

UPDATE:

If you get the field as Date from your Database, it will be DateTime in C#. And DateTime always contains a Time Component. So what you can do is to modify the result from your StoredProcedure. How you can achieve this is described here.

Change the return Type of your StoredProcedure to string, so you can correctly show the information in your ListView:

SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10);
Community
  • 1
  • 1
Sebi
  • 3,879
  • 2
  • 35
  • 62
  • I have tried adding "yyy-MM-dd" as sugested, but then I get this error: "No overload for method 'ToString' takes 1 arguments" – ExpressDude Nov 02 '16 at 09:25
  • Using "ListViewItem FeedbackTable = new ListViewItem(Convert.ToDateTime(FeedbackReader["DateCaptured"].ToString()));" gives me this error: " Argument1: connot convert from 'System.DateTime' to 'String' " – ExpressDude Nov 02 '16 at 09:29
  • If your Argument expects a DateTime you cannot give it a string. A DateTime always got the Time Component. Use the ToString where you print your result on Konsole, Form, Report etc. – Sebi Nov 02 '16 at 09:38
  • I get my info from stored procedure in SQL. Then I use a Reader to read the returning results and dump it in the listview. Not sure where or how to make a change so that only the time shows. – ExpressDude Nov 02 '16 at 09:52
  • @ExpressDude See my Update. Changing your SQL is the solution. Hope you are able to modify your statement/procedure. – Sebi Nov 02 '16 at 11:08
0

Because a DateTime is a DateTime, containing both Date and Time part. And DateTime.ToString() is implementented so it display also the Time part (which is 00:00, as result of your query)

Use the appropriate formatting for DateTime: e.g 

myDateTime.ToString("d"); //short date pattern

or

myDateTime.ToString("dd/MM/yyyy"); // your custom display string

Now you have to create a ListViewItem with your correct date format; try this:

ListViewItem FeedbackTable = new ListViewItem(Convert.ToDateTime(FeedbackReader["DateCaptured"]).ToString("d"));

i.e., first you convert FeedbackReader["DateCaptured"]) (which is defined as object) to a DateTime, then apply the correct formatting (with ToString("d");) and then create your listviewItem with the desired string

Gian Paolo
  • 4,161
  • 4
  • 16
  • 34
  • Are you referring to when one submit the date with the DateTime picker to SQL? – ExpressDude Nov 02 '16 at 09:27
  • No, i mean that FeedbackReader["DateCaptured"] returns a DateTime, with year, month, day, hour, minute, second and fraction of second. In your case, the time part is 00:00.0000. This does not mean that DateTime.ToString() doesn't display the time part – Gian Paolo Nov 02 '16 at 09:33
  • ok I understand. But I am still stuck in only getting the date to display in the listview. – ExpressDude Nov 02 '16 at 09:50
0

I have found my solution. Logic actually, but so I learn...

I changed in SQL procedure this:

CONVERT (DATE,RecordAdded, 103) as DateCaptured,

To:

CONVERT (VARCHAR(10),RecordAdded, 103) as DateCaptured,

Now SQL converts it to a string after formatting it, giving me the result I want.

The Result I wanted

ExpressDude
  • 45
  • 10
  • As you say, this works, but my advice is not to use this pattern: with the query you should get the data you need (in this case, a date), and with the code you should format your date according to your needs. Keep things separated: retrieve data from display them – Gian Paolo Nov 02 '16 at 10:26