Returning only date from ToDateTime

Question

Heads up, I'm fairly new to working with Databases so this is an elementary level question.

I'm trying to pull the date from the SQL Database but it returns both the date and time. In SQL, I have the type as date so it isn't recording any time yet it continues to return something like 01/12/2002 12:00:00. I'm assuming the issue is in my C# code by using the ToDateTime() method since it is meant to return time as well.

All I want is to pull the date in MM/dd/yyyy formatting.

I've looked all over and have tried countless suggestions I've found on here and other sites but still, nothing has solved the problem. I've tried TryParse, converting to a string, and a few other ways to try and work it out.

Thanks for any help you all can provide. Much appreciated!

while (reader.Read())
{
    EventModel eventModel = new EventModel();
    eventModel.EventType = reader["EventType"].ToString();
    eventModel.Title = reader["Title"].ToString();
    eventModel.Date = Convert.ToDateTime(reader["Date"]);
    eventModel.Location = reader["Location"].ToString();
    eventModel.Company = reader["Company"].ToString();

    allEvents.AddLast(eventModel);
}

CREATE TABLE [dbo].[Events](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EventType] [varchar](50) NOT NULL,
    [Title] [varchar](50) NOT NULL,
    [Date] [date] NOT NULL,
    [Location] [varchar](50) NOT NULL,
    [Company] [varchar](50) NULL
) ON [PRIMARY]
GO

What's the *SQL Database*? E.g. Oracle's DATE datatype is actually a timestamp. In Standard SQL `CAST(timestampcolumn AS DATE)` strips off the time portiton. — dnoeth, Aug 27 '20 at 20:11
Please tag the DBMS. Is it SQL Server? Your tag `sql` simply indicates that we're taking about Structured Query Language. — Lars Skaug, Aug 27 '20 at 20:24
Try `SELECT convert(varchar(10), [Date], 101)` to let the database do the conversion froom DATE to string — dnoeth, Aug 27 '20 at 20:27
Does this answer your question? [Convert.ToDateTime: how to set format](https://stackoverflow.com/questions/15203534/convert-todatetime-how-to-set-format) — derloopkat, Aug 27 '20 at 20:32
@derloopkat Tried some suggestions but it's still not working — Sam, Aug 27 '20 at 20:46

score 3 · Accepted Answer · edited Aug 27 '20 at 21:34

You said you want to pull the date formatted. The DateTime object doesn't contain any formatting. If no time is supplied, then the DateTime's time component will default to midnight. You need to add the formatting to display the date component only.

You have a few options. You could format the date straight out of the database, but you didn't post your SQL query. Without adjusting your query, you could do something as simple as changing your eventModel.Date to a String instead of a DateTime and then:

eventModel.Date = DateTime.Parse(reader["Date"]).ToString("MM/dd/yyyy");

The ToString() function formats the output the way you want it to display. Maybe you tried calling ToString() without the formatting parameter. I changed the Convert.ToDateTime to DateTime.Parse as that is what Convert.ToDateTime will call anyway.

On a side note, take a look at Entity Framework for working with the database, it's a great time saver.

I changed eventModel.Date to a string and used this to line and seems to be working correctly. ```eventModel.Date = Convert.ToDateTime(reader["Date"].ToString()).ToShortDateString(); ``` — Sam, Aug 27 '20 at 21:02

score 0 · Answer 2 · answered Aug 27 '20 at 20:43

0

Try something like below to return mm/dd/yyyy

   eventModel.Date = Convert.ToDateTime(reader["Date"].ToString()).ToShortDateString();

answered Aug 27 '20 at 20:43

JobesK

347
1
2
6

says I can't convert type string to datetime – Sam Aug 27 '20 at 20:53
Is the reader returning a valid date? (it wont work if its null) Also what type is eventModel.Date? – JobesK Aug 27 '20 at 21:03
It's a valid date and they type was Datetime but I just changed it to string and now it's working. – Sam Aug 27 '20 at 21:10

Returning only date from ToDateTime

2 Answers2