Mapping SQL Server datatype (date,datetime,smalldatetime) in C#

Question

_{Wednesday, July 22, 2015 9:29 AM}

Hi,

I need to show date column from sql server in C# as dd/mm/yyyy and if column datatype is datetime then i need to show as dd/mm/yyyy hh:mm:ss

How can i achieve this ?

Thanks

All replies (11)

_{Wednesday, July 22, 2015 11:13 AM ✅Answered}

There shouldn't be a problem for you to use Custom Date and Time Format Strings.

https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

chanmm

chanmm

_{Wednesday, July 22, 2015 11:55 AM ✅Answered | 3 votes}

SQL server has a Date type which is a day.

c# doesn't, it has datetime.

Meaning that a date or datetime in sql server will be detected as datetime by the method you are using - it will not work.

You could infer it by using Datetime.TimeOfDay

TimeSpan ts = UnknownDateTime.TimeOfDay;

Then check if ts has any time in it.

If you ever happen to have a datetime of exactly midnight then that's going to mess you up though.

Hope that helps.

Technet articles: WPF: MVVM Friendly User Notification; All my Technet Articles

_{Wednesday, July 22, 2015 12:28 PM ✅Answered}

Hi,

reader.GetFieldType(int ordinal) 

will return the .NET type of the field, while:

reader.GetDataTypeName(int ordinal) 

will return a string representing the data type of the field in the data source (e.g. varchar). GetFieldType is likely to be more useful to you given the use case you describe

Thank You,

Sammani

http://sammanipalansuriya.blogspot.com/

_{Wednesday, July 22, 2015 3:54 PM ✅Answered}

The approach that we use is that we have a custom Date type. I blogged about it here.  In your data access layer if the column is a DATE then use the custom Date type otherwise use a normal DateTime.  Date and DateTime are interchangeable so the value itself doesn't change.  But in your UI you can programmatically detect Date vs DateTime and format accordingly. Or, even better simply use ToString on the object and let the default string display be used.

Michael Taylor
http://blogs.msmvps.com/p3net

_{Wednesday, July 22, 2015 9:52 AM}

Are you using Windows forms, WPF, something else?

_{Wednesday, July 22, 2015 11:01 AM}

I am using Sql Command to execute the stored procedure and retrieving this result in SQLDataReader

After getting this result I am checking the metadata (using GetSchemaTable) of the each column to identify the column DataType .

Even when I have casted my column datatype as date in Stored Procedure, i cannot see this datatype in metadata of Datareader but instead I am getting this column data type as **System.DateTime **

Then On UI(Win form) based on this datatype, I want to apply formats 

dd/mm/yyyy if date

dd/mm/yyyy hh:mm:ss if datetime

_{Wednesday, July 22, 2015 11:33 AM}

Which control are you binding this Data to? Did you use Date and DateTime DataTypes in SQL?

You can also apply the format directly with the T-SQL statment:

http://www.w3schools.com/sql/func_convert.asp

Fouad Roumieh

_{Thursday, July 23, 2015 6:40 PM}

Of course the elephant in the room is you could just define some formatting on the UI.

Either directly or via some metadata rather than the type directly.

You could use a custom dataannotation on your property in your entity class or DTO ( hey Danny ) or whatever you pass it through using.

Hope that helps.

Technet articles: WPF: MVVM Friendly User Notification; All my Technet Articles

_{Thursday, July 23, 2015 6:46 PM}

The approach that we use is that we have a custom Date type. I blogged about it here.  In your data access layer if the column is a DATE then use the custom Date type otherwise use a normal DateTime.  Date and DateTime are interchangeable so the value itself doesn't change.  But in your UI you can programmatically detect Date vs DateTime and format accordingly. Or, even better simply use ToString on the object and let the default string display be used.

Michael Taylor
http://blogs.msmvps.com/p3net

Interesting blog post Michael.

( Reaches for TnWiki councillor hat ).

I just took a look, can't see it there.  Have you considered copying that into a TnWiki article?

Gets you some more recog points and you could enter it into the guru competition.

Another angle for your mvp as well.

Hope that helps.

Technet articles: WPF: MVVM Friendly User Notification; All my Technet Articles

_{Thursday, July 23, 2015 6:56 PM}

"You could use a custom dataannotation on your property in your entity class "

There is already a DataType annotation that allows you to specify this so you wouldn't need a custom annotation.  It is used in MVC quite a bit.

_{Thursday, July 23, 2015 6:57 PM}

"Interesting blog post Michael."

Thanks.  Haven't considered posting it elsewhere.  Not too interested in gaining points.  I do this for the fun of it :}