ASP.NET Date Manipulation
In this article I want to describe the date manipulation in c# using SQL SERVER this article covers storing date and time in SQL data base as well as manipulation with c# programming using various date formats which are used on the ASP.NET UI forms.
How the SQL server stores date in the database : IN the SQL server datetime field stores the date in the format of dd/mm/yy hh:mm:ss . if the time is not mentioned the time is stored default as 12:00:00 AM . It can be seen by these two screen shots
Screen shot1 as web page where there are two date fields one is Date of Birth and second one is session date , in the date of birth field three drop down list are dynamically used however the end user can select the date in dd/mm/yy format , in the same way the user can select the session date by using calendar in dd/mm/yy format it is a illusion of the end user that he is able to select the date in dd/mm/yyyy format but actually the dates are stored in the SQL server only in mm/dd/yyyy format this can be easily verified by the label session date where the date has been selected by the calendar is displayed on the label in mm/dd/yy format .How these dates are stored in the SQL server it is displayed in the next screen shot2 and screenshot3 .
Screen shot1 selecting the dates by drop down list or using calendar:
So it is clear that in the database the date is stored in mm/dd/yyyy then how can we display the only date , only time , both date and time in the desired format on the web page .
Here is the various format to display the date and time .
|
Format
Specifier
|
Pattern
|
Output displayed ( Example)
|
|
t
|
h:mm tt
|
4:05 PM
|
|
d
|
M/d/yyyy
|
3/9/2013
|
|
T
|
h:mm:ss tt
|
4:05:07 PM
|
|
D
|
dddd, MMMM dd, yyyy
|
Sunday, March 09, 2013
|
|
f
|
dddd, MMMM dd, yyyy h:mm tt
|
Sunday, March 09, 2013 4:05 PM
|
|
F
|
dddd, MMMM dd, yyyy h:mm:ss tt
|
Sunday, March 09, 2013 4:05:07 PM
|
|
g
|
M/d/yyyy h:mm tt
|
3/9/2013 4:05 PM
|
|
G
|
M/d/yyyy h:mm:ss tt
|
3/9/2013 4:05:07 PM
|
|
m,M
|
MMMM dd
|
March 09
|
|
y,Y
|
MMMM, yyyy
|
March, 2013
|
|
r, R
|
(RFC1123Pattern)
|
ddd, dd MMM yyyy HH':'mm':'ss 'GMT'
|
Example:
Suppose we want to display the date time 2013-03-09 16:05:07.123
DateTime dt = new DateTime(2013, 3, 9, 16, 5, 7, 123);
An example to use of these format specifier in string.Format method .
String.Format("{0:t}", dt); // "4:05 PM"
String.Format("{0:d}", dt); // "3/9/2013"
Custom DateTime Formatting: Suppose if we don’t get our desired format in the above table we can design our own custom format .
Where format specifiers y (year), M (month), d (day), h (hour 12), H (hour 24), m (minute), s (second), f (second fraction), F (second fraction, trailing zeroes are trimmed), t (P.M or A.M)
Various ways to display date time custom format :
|
Name
|
Format
|
Display format
|
|
Year
|
y yy yyy yyyy
|
9 09 009 0009
|
|
Month
|
M MM MMM MMMM
|
3 03 Mar March
|
|
Day
|
d dd ddd dddd
|
9 09 Sun Sunday
|
|
Hour 12/24
|
h hh H HH
|
5 05 17 17
|
|
Minute
|
m mm
|
5 05
|
|
Seconds
|
s ss
|
7 07
|
|
Second Fraction
|
f ff fff ffff
|
1 12 123 1230
|
|
Second Fraction without zeros
|
F FF FFF FFFF
|
1 12 123 123
|
|
A.M. or P.M.
|
t tt
|
P PM or A AM
|
|
Time Zone
|
z zz zzz
|
"-6 -06 -06:00
|
(You can use , - or / according to your requirement.)
Example :
DateTime dt = DateTime.Now; //out put will be 3/19/2013 11:17:29
DateTime dt = DateTime.Today.Date;//label 3/19/2013 12:00:00
Label1.Text = DateTime.Now.ToString("d/M/yyyy");//out put 9/3/2013
Label1.Text = DateTime.Now.ToString("dd/MM/yyyy");//out put 09/03/2013
Label1.Text = DateTime.Now.ToString("MMMM, ddd dd, HH:mm yyyy”); //out put 09/03/2013
Date manipulation in C #
As you know that the static members of the class are accessed by the class there is no need to create the object of the class so for using the current date time we can use the static property of the DateTime class .
Example :
DateTime dt = DateTime.Now; //It displays the today's date with current time
e.g.6/19/2012 12:00:00
DateTime dt = DateTime.Today.Date; //It displays the today's date with start time of the date e.g.6/19/2012 12:00:00 (Takes the system time )
Date Manipulation : While manipulation with dates you should care of the date format because of date can be subtracted , compared or added only when the date is taken in the mm/dd/yyyy format while you can display the date or time in any your desired format.
Calculating the difference between two dates adding days in the start date :
In this example the end date is calculated after adding 365 days and the end date is displayed in local time we know generally the system time is set to the time zone of the local time .
Example 1:
protected void Button1_Click1(object sender, EventArgs e)
{
DateTime StartDate = DateTime.Today.Date;
DateTime EndDate = StartDate.AddDays(365);
TimeSpan ts = EndDate.Subtract(StartDate);
Label1.Text = "total days" + ts.Days.ToString();
Label2.Text = "hours" + ts.Hours.ToString();
Label3.Text = "Minutes" + ts.Minutes.ToString();
Label4.Text = "today date" + StartDate.ToString();
Label5.Text = "Miliseconds" + ts.Milliseconds.ToString();
Label6.Text = "difference" + ts.ToString();
EndDate = EndDate.ToUniversalTime();
DateTime EndDateLocalTime = EndDate.ToLocalTime();
Label8.Text = "End date loacl time " + EndDateLocalTime.ToString();
}
Output of the Program:
total days365
hours0
Minutes0
today date7/1/2012 12:00:00 AM
Miliseconds0
difference365.00:00:00
End date loacl time 7/1/2013 12:00:00 AM
In the above example the difference is calculated after adding for 365 days then end date is displayed at the local time .
Example 2: In this example the difference is calculated after adding 365 days to the today’s date the end date is displayed in the UTC date time as for time zone the difference is (UTC+ 5.30) that is why there is a difference of 5.30 hours.
protected void Button1_Click(object sender, EventArgs e)
{
DateTime StartDate = DateTime.Today.Date;//Takes the system date time 12:00 A.M.
DateTime EndDate = StartDate.AddDays(365);
TimeSpan ts = EndDate.ToUniversalTime().Subtract(StartDate);
TimeSpan ts = EndDate.Subtract(StartDate);
txtDifference.Text = ts.ToString();
Label1.Text = "total days" + ts.Days.ToString();
Label2.Text = "hours" + ts.Hours.ToString();
Label3.Text = "Minutes" + ts.Minutes.ToString();
Label4.Text = "today date" + StartDate.ToString();
Label5.Text = "END DATE UTC" + EndDate.ToUniversalTime().ToString();
Label6.Text = "Miliseconds" + ts.Milliseconds.ToString();
Label7.Text = "difference" + ts.ToString();
}
Output :
total days364
hours18
Minutes30
today date 6/21/2012 12:00:00 AM
END DATE UTC6/20/2013 6:30:00 PM
Miliseconds0
difference364.18:30:00