ASP.NET date formatting issue

Question

I have a SQL Server database which has a column of date with value of 2001-12-23. The insert statement makes sure that the input is correctly formatted using the following code:

var start_date_formatted = DateTime.ParseExact(start_date.Text, "dd-MM-yyyy", null);

Even though the input is formatted as dd-MM-yyyy it still appears in the database as 2001-12-23.

The thing that I don't understand is the fact that whenever I query the data from the database, it returns the date correctly formatted dd-MM-yyyy even though it appears in the database as yyyy-MM-dd.

That is not a big deal since I got the formatting I need eventually.

The problem is that the time string is being added to my date generating the output of 23-12-2001 00:00:00. I need date only but can't figure out why is it being added and why can't I change it using the following method:

DateTime.ParseExact(startdate, "dd-MM-YYYY", null); // string is not recognized as valid DateTime

or

.ToString("dd-MM-YYYY"); // no overload method ToString that takes 1 argument

Since I'm new to asp.net I would like to get to know how to handle date formats and if there is any general config setting which would require and set certain date format?

UPDATE

var dt = BookingAccess.ManageBookingsDataTable();
            string id = string.Empty;
            string name = string.Empty;
            string startdate = string.Empty;
            string enddate = string.Empty;
            string full_string = string.Empty;
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    id = dt.Rows[i]["id"].ToString();
                    var sdate = dt.Rows[i]["start_date"];
                    name = dt.Rows[i]["Pet name"].ToString();
                    startdate = dt.Rows[i]["start_date"].ToString("dd-MM-yyyy"); // error is thrown here
                    enddate = dt.Rows[i]["end_date"].ToString();
                    full_string = startdate + " to " + enddate + " (" + name + ")";
                    CurrentBookings.Items.Add(new ListItem(full_string, id));
                }
            }

This does not seem to format the date either:

startdate = string.Format("{0:dd/MM/yyyy}", dt.Rows[i]["start_date"].ToString());

Kirill Bestemyanov · Accepted Answer · 2012-11-22 21:33:42Z

2

In C# there is no Type Date, so all dates is variables of DateTime type. So when you get date from db it transfers into DateTime variable with time 00:00:00

To get string from this you can use ToString("dd-MM-yyyy"):

var dt = DateTime.Now;
string dateinstring = dt.ToString("dd-MM-yyyy")); //it contains "23-11-2012"

Update:

Your problem is that your datatable is not typed, so your startdate is object, not DateTime. You should cast it to DateTime:

startdate = ((DateTime)dt.Rows[i]["start_date"]).ToString("dd-MM-yyyy");

This will works if your dt.Rows[i]["start_date"] is not nullable, in other case you should check that it is not null before casting.

edited Nov 22, 2012 at 21:33

answered Nov 22, 2012 at 21:08

Kirill Bestemyanov

12k2 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jacek_podwysocki Over a year ago

I used ToString method but ut returns error that there is no method that takes 1 argument

Kirill Bestemyanov Over a year ago

Could you show not working code? I'm test my code (that is in my answer) right now and it works correctly.

Henk Mollema · Accepted Answer · 2012-11-22 21:50:37Z

0

You shouldn't worry about the display format of a DateTime in SQL Server. In my SQL management studio they also appear as 2001-12-23, but in my Visual Studio as 23-12-2001.

You probably set the Column Data Type to DateTime. This adds a time of 00:00:00, if none specified. You can specify the Date type if you only want to store a date, although it doesn't really matter if you format the DateTime on your website.

--

Update:

Cast the value from the database to a DateTime, then format it:

DateTime date = Convert.ToDateTime(dt.Rows[i]["start_date"]); 
string startdate = date.ToString("dd-MM-yyyy");

edited Nov 22, 2012 at 21:50

answered Nov 22, 2012 at 21:07

Henk Mollema

46.9k12 gold badges101 silver badges107 bronze badges

8 Comments

jacek_podwysocki Over a year ago

I checked again and my column has a type of date which I assume should prevent the time string to even be taken under consideration when inserting and/or retrieving data from database

Henk Mollema Over a year ago

What database designer do you use?

jacek_podwysocki Over a year ago

Nothing special, I use generic MS SQL Management Studio to create and manage my database

Henk Mollema Over a year ago

A column with data type Date doesn't show a time for me in MS SQL Server Management Studio. Are sure it's a Date column?

Henk Mollema Over a year ago

Try DateTime date = Convert.ToDateTime(dt.Rows[i]["start_date"]); string dateString = date.ToString("dd/MM/yyyy"); Or you could use date.ToShortDateString() but that might give some culture issues.

|

Collectives™ on Stack Overflow

ASP.NET date formatting issue

2 Answers 2

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related