Click here to Skip to main content
15,944,737 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a column in a database that is customized in the following format:
SQL
convert(varchar, PERSON.DateOfBirth, 103) as DateOfBirth


I'm trying to show a table in Grid View using a list and a data reader from Data Access Layer (not from presentation layer) and the problem exists by field where the value of DateOfBirth is declared as DateTime in a class and a database.
I'm trying to read this field from database as following:
C#
while (reader.Read())
                        {
                            if (!reader.IsDBNull(0))
                            {
                                Person person= new Person();
                                person.ID_Person = reader.GetInt32(0);
                               
                                //here are more rows
                                person.DateOfBirth = reader.GetDateTime(4);
                                //and here are some more rows

                                ListPerson.Add(person);
                            }

                        }
                        reader.Close();

The row where I'm trying to read customized datetime, it throws me an error and can not read that type of Date.
Can anyone help me to fix this and read the date format from database.
Thank you in advance for your reply.
Cheers.
Posted

1 solution

Why are you using a varchar value for a datetime in the first place?
The database have to be culture-agnostic. The localization have to be done on the client side, according to regional settings of the user account it runs in.
In other words, you take care of the datetimes formatting when you display them to the client, not when you store them in the database.

You did not create a customized datetime format in db, you just created your worst nightmare. Your DB design is flawed, and you should correct that in the first place, i.e. use datetime type for datetime values. Doesn't it make sense?

Hope this helps :)

[Edit] Completed from your comment

Here's where you will find informations on displaying a DateTime according to specific culture informations:
DateTime.ToString() Method[^]
Formatting Types in the .NET Framework[^]
Standard Date and Time Format Strings[^]
Custom Date and Time Format Strings[^]
 
Share this answer
 
v3
Comments
dr_iton 3-Dec-15 19:08pm    
Ok I can return the database column in DateTime format, but I want to show that column as a short date time format in grid view DateTime.ToString("dd-MM-yyyy")

Thank you for fast reply.
Cheers
George Jonsson 4-Dec-15 2:38am    
So what is the problem with that?
dr_iton 4-Dec-15 3:26am    
The problem was that I never used this before, solved the problem after I added a property in the boundfield. In case that someone else has the same problem the solution is to give a property to the column of datetime:
dataformatstring="{0:dd-MM-yyyy}" HtmlEncode="false"
Cheers.
phil.o 4-Dec-15 3:55am    
It would have been difficult to give a precise answer as you did not state that you were using ASP.NET. Handling things is not done the same way in Windows Forms, WPF/Silverlight, ASP.NET; you should tag your question precisely with the technology you are working with if you want precise and meaningful answers. Cheers, and good work :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900