Having problem with customized DateTime fromat in DB using DataReader

Question

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 3-Dec-15 12:51pm

dr_iton

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

phil.o · Accepted Answer · 2015-12-03T12:59:00

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[^]