Set Date/Time Format for a Web Page

Introduction

We all know that to retrieve values from a database we have to pass on a "Select" query to it (in case you didn't, then this is not the place where you should be). Though it works perfectly in the localhost for all datatypes, if uploaded to the server, a DateTime field may sometimes throw a FormatException while parsing it to a .NET DateTime variable. The reason for this is that the database may not return the date in compliant to the server. Therefore, we have to set the format through coding.

Background

You need to have a clear understanding of ASP.NET and SQL Server.

Using the Code

Here we will use the class CultureInfo defined in the namespace System.Globalization.

Now in the Page_Load event, write the following:

System.Globalization.CultureInfo vCulture =
   (System.Globalization.CultureInfo)System.Globalization.CultureInfo.CreateSpecificCulture("en-US").Clone();
//set the format here
vCulture.DateTimeFormat.ShortDatePattern = "MM/dd/yyyy";

System.Threading.Thread.CurrentThread.CurrentUICulture = vCulture;

While retrieving data from a DataReader (or you can also use a DataAdapter), first convert the DateTime value in the format defined in the Page_Load event, then cast it into a .NET DateTime variable.

//Datefixed is the name of the Date Column in the Database
string temp = String.Format("{0:d}", dr["Datefixed"]);
BDPlite1.SelectedDate = Convert.ToDateTime(temp); //BDPlite1 is a custom DateTime picker

The format of the small 'd' has been specified above using the vCulture object because d represents ShortDatePattern. For details, see the table below..

Points of Interest

This is indeed a very tricky situation as this error is caught only when we run the application on the server. Therefore your application will run smoothly on localhost whereas a FormatException will be thrown while attempting to run it on the server.

Following is the list of date formats supported in C#:

Specifier               DateTimeFormatInfo property               Pattern value(for en-us culture)
---------------------------------------------------------------------------------------------------
t                       ShortTimePattern                          h:mm tt
d                       ShortDatePattern                          M/d/yyyy
T                       LongTimePattern                           h:mm:ss tt
D                       LongDatePattern                           dddd, MMMM dd, yyyy
f                      (combination of D and t)                   dddd, MMMM dd, yyyy h:mm tt
F                       FullDateTimePattern                       dddd, MMMM dd, yyyy h:mm:ss tt
g                      (combination of d and t)                   M/d/yyyy h:mm tt
G                      (combination of d and T)                   M/d/yyyy h:mm:ss tt
m, M                    MonthDayPattern                           MMMM dd
y, Y                    YearMonthPattern                          MMMM, yyyy
r, R                    RFC1123Pattern                            ddd, dd MMM yyyy HH':'mm':'ss 'GMT' (*)
s                       SortableDateTimePattern                   yyyy'-'MM'-'dd'T'HH':'mm':'ss (*)
u                       UniversalSortableDateTimePattern          yyyy'-'MM'-'dd HH':'mm':'ss'Z' (*)

The sample output is displayed below:

String.Format("{0:t}", dt); // "4:05 PM" ShortTime
String.Format("{0:d}", dt); // "4/10/2012" ShortDate
String.Format("{0:T}", dt); // "4:05:07 PM" LongTime
String.Format("{0:D}", dt); // "Tuesday, April 10, 2012" LongDate
String.Format("{0:f}", dt); // "Tuesday, April 10, 2012 4:05 PM" LongDate+ShortTime
String.Format("{0:F}", dt); // "Tuesday, April 10, 2012 4:05:07 PM" FullDateTime
String.Format("{0:g}", dt); // "4/10/2012 4:05 PM" ShortDate+ShortTime
String.Format("{0:G}", dt); // "4/10/2012 4:05:07 PM" ShortDate+LongTime
String.Format("{0:m}", dt); // "April 10" MonthDay
String.Format("{0:y}", dt); // "April, 2012" YearMonth
String.Format("{0:r}", dt); // "Tue, 10 Apr 2012 16:05:07 GMT" RFC1123
String.Format("{0:s}", dt); // "2012-04-10T16:05:07" SortableDateTime
String.Format("{0:u}", dt); // "2012-04-10 16:05:07Z" UniversalSortableDateTime

For more info on Standard Date and Time Format Strings, click here.