C# Date Time

http://www.java2s.com/Tutorial/CSharp/0260__Date-Time/Catalog0260__Date-Time.htm

13. 1. DateTime( 21 )	13. 5. TimeSpan( 17 )
	13. 2. DateTime Now( 2 )	13. 6. TimeSpan Parse( 2 )
	13. 3. DateTime Format( 13 )	13. 7. Stopwatch( 1 )
	13. 4. DateTime Parse ParseExact( 10 )

Convert DateTime

CREATE PROCEDURE sproc_UpdateTemp_Sub
(
@PreviousDay datetime,
@PresentDay datetime
)
AS

select * from table1 where date between @previousday and @presentday

Go

CREATE PROCEDURE sproc_UpdateTemp
AS

DECLARE @PreviousDay datetime
DECLARE @PresentDay datetime

select @PresentDay = convert( datetime, convert( varchar(10), getdate(), 101 ))
select @PreviousDay = dateadd( day, -1, @PresentDay )

exec sproc_UpdateTemp_Sub @PreviousDay, @PresentDay

Go

Declare @presentday as varchar(10)
SET @PresentDay =  convert( varchar(10), getdate(), 101 )
select * from table where Convert( varchar(10), date1, 101)  = @presentday

Why are you using varchar instead of datetime datatype? If it is just to lose the time part of GetDate it is much more efficient convert it using a float.

Something like

select cast(floor(convert( float,getdate())) as datetime)

returns you today’s date with the time part set to 00:00:00.

You could create a UDT like this:

create function dbo.fnDateFromDateTime(@da

tetime datetime)
returns datetime
AS
begin
return cast(floor(convert( float,@datetime)) as datetime)
end

And call it like this:

select dbo.fnDateFromDateTime(GetDate())

Insert Null value into DateTime column in SQL server from ASPX application

http://blogs.msdn.com/jaskis/archive/2008/07/09/insert-null-value-into-datetime-column-in-sql-server-from-aspx-application.aspx

Scenario
========

We have following web form asking user to enter name and joining date

 

In SQL express we are having following columns

Error
======
When ever user does not enter joining date instead of getting null value added into join_date column we end with exception on browser

Code Behind
===========

    SqlCommand cmd1 = new SqlCommand("INSERT INTO emp(emp_name, join_date) VALUES(@emp_name1,@join_date1)", con1);

    cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar).Value = name.Text;
    cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime).Value = joindate.Text;

    cmd1.ExecuteNonQuery();
    Label1.Text = " Record added";

 

Solution
========

Make a check(if) for empty string and insert specific SqlDateTime.null value

C#

cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar);
cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime);

cmd1.Parameters["@emp_name1"].Value = name.Text;

System.Data.SqlTypes.SqlDateTime getDate;
//set DateTime null
getDate = SqlDateTime.Null;

if (joindate.Text == "")
{
    cmd1.Parameters["@join_date1"].Value = getDate;
}
else
{
    cmd1.Parameters["@join_date1"].Value = joindate.Text;
}

 

VB.NET

cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar)
cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime)

cmd1.Parameters("@emp_name1").Value = name.Text

Dim getDate As SqlDateTime
getDate = SqlDateTime.Null

If joindate.Text = "" Then
   cmd1.Parameters("@join_date1").Value = getDate
Else
   cmd1.Parameters("@join_date1").Value = joindate.Text
End If

 

Hope this helps!!

Enter Null Values for DateTime Column of SQL Server

http://www.c-sharpcorner.com/UploadFile/sd_patel/EnterNullValuesForDateTime11222005015742AM/EnterNullValuesForDateTime.aspx

Inserting a null value to the DateTime Field in SQL Server is one of the most common issues giving various errors. Even if one enters null values the value in the database is some default value as 1/1/1900 12:00:00 AM.

The Output of entering the null DateTime based on the code would in most cases have errors as:

• String was not recognized as a valid DateTime.
• Value of type ‘System.DBNull’ cannot be converted to ‘String’.

Or No Error but DataTime entered in Database would be as 1/1/1900 12:00:00 AM
So lets write the code to enter null values in the DataBase.

 

 

The User Interface is as follows:

 

 

To begin with Code:

Namespaces used

• System.Data.SqlClient/ System.Data.OleDb

• System.Data.SqlTypes
• Code for System.Data.SqlClient

C#

string sqlStmt ;
string conString ;
SqlConnection cn =null;
SqlCommand cmd =null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) “;
conString = “server=localhost;database=Northwind;uid=sa;pwd=;”;
cn = new SqlConnection(conString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(new SqlParameter(“@FirstName”, SqlDbType.NVarChar, 11));
cmd.Parameters.Add(new SqlParameter(“@LastName”, SqlDbType.NVarChar, 40));
cmd.Parameters.Add(new SqlParameter(“@Date”, SqlDbType.DateTime));
sqldatenull = SqlDateTime.Null;
cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
cmd.Parameters["@LastName"].Value = txtLastName.Text;
if (txtDate.Text == “”)
{
cmd.Parameters ["@Date"].Value =sqldatenull ;
//cmd.Parameters["@Date"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@Date"].Value = DateTime.Parse(txtDate.Text);
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Record Inserted Succesfully”;
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

VB.NET

Dim sqlStmt As String
Dim conString As String
Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim sqldatenull As SqlDateTime
Try
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) “
conString = “server=localhost;database=Northwind;uid=sa;pwd=;”
cn = New SqlConnection(conString)
cmd = New SqlCommand(sqlStmt, cn)
cmd.Parameters.Add(New SqlParameter(“@FirstName”, SqlDbType.NVarChar, 11))
cmd.Parameters.Add(New SqlParameter(“@LastName”, SqlDbType.NVarChar, 40))cmd.Parameters.Add(New SqlParameter(“@Date”, SqlDbType.DateTime))
sqldatenull = SqlDateTime.Null
cmd.Parameters(“@FirstName”).Value = txtFirstName.Text
cmd.Parameters(“@LastName”).Value = txtLastName.Text
If (txtDate.Text = “”) Then
cmd.Parameters(“@Date”).Value = sqldatenull
‘cmd.Parameters(“@Date”).Value = DBNull.Value
Else
cmd.Parameters(“@Date”).Value = DateTime.Parse(txtDate.Text)
End If
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = “Record Inserted Succesfully”
Catch ex As Exception
Label1.Text = ex.Message
Finally
cn.Close()
End Try

Code for System.Data.SqlClient.

C#

string sqlStmt;
string conString ;
OleDbConnection cn =null ;
OleDbCommand cmd =null ;
try
{
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (?,?,?) “;
conString = “Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost”;
cn =new OleDbConnection(conString);
cmd = new OleDbCommand(sqlStmt, cn) ;
cmd.Parameters.Add(new OleDbParameter(“@FirstName”, OleDbType.VarChar, 40));
cmd.Parameters.Add(new OleDbParameter(“@LastName”, OleDbType.VarChar, 40));
cmd.Parameters.Add(new OleDbParameter(“@Date”, OleDbType.Date));
cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
cmd.Parameters["@LastName"].Value = txtLastName.Text;
if ((txtDate.Text == “”) )
{
cmd.Parameters["@Date"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@Date"].Value = DateTime.Parse(txtDate.Text);
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Record Inserted Succesfully”;
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
} 

VB.NET

Dim sqlStmt As String
Dim conString As String
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Try
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (?,?,?) “
conString = “Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost”
cn = New OleDbConnection(conString)
cmd = New OleDbCommand(sqlStmt, cn)
cmd.Parameters.Add(New OleDbParameter(“@FirstName”, OleDbType.VarChar, 40))cmd.Parameters.Add(New OleDbParameter(“@LastName”, OleDbType.VarChar, 40))cmd.Parameters.Add(New OleDbParameter(“@Date”, OleDbType.Date))cmd.Parameters(“@FirstName”).Value = txtFirstName.Text
cmd.Parameters(“@LastName”).Value = txtLastName.Text
If (txtDate.Text = “”) Then
cmd.Parameters(“@Date”).Value = DBNull.Value
Else
cmd.Parameters(“@Date”).Value = DateTime.Parse(txtDate.Text)
End If
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = “Record Inserted Succesfully”
Catch ex As Exception
Label1.Text = ex.Message
Finally
cn.Close()
End Try

DateTime Manipulation

http://www.blackwasp.co.uk/CSharpDateManipulation.aspx

The twenty-seventh part of the C# Fundamentals tutorial completes the examination of the DateTime data type provided by C# and the .NET Framework. This article considers manipulation of DateTime data and formatting date and time information as a string.

>> Download Source Code

<!–
google_ad_client = “pub-3760328251384501″;
/* Inline Rectangle */
google_ad_slot = “1524970617″;
google_ad_width = 300;
google_ad_height = 250;
//–>

window.google_render_ad();

DateTime Value Manipulation

The previous articles in the C# Fundamentals tutorial have examined the creation and reading of DateTime data and the structure’s constituent parts. In this article, the manipulation of DateTime information is described, starting with the methods by which DateTime values can be adjusted. This can be achieved using simple operators or, for more detailed control, using the provided DateTime methods.

Addition and Subtraction Operators

Two arithmetic operators are provided for use with the DateTime structure. These are the addition (+) and subtraction (-) operators. Each operator requires two operands. The first operand is the DateTime value to be modified and the second is a TimeSpan value containing the duration of time to add or remove from the value. In the following examples, a TimeSpan value is created as required using the new keyword and a TimeSpan constructor.

DateTime theDate = DateTime.Parse("2 Jan 2007 20:15:00");

// Add one hour, one minute and one second
theDate = theDate + new TimeSpan(1, 1, 1);    // theDate = 2 Jan 2007 21:16:01

// Subtract twenty-five days
theDate = theDate - new TimeSpan(25, 0, 0, 0);  // theDate = 8 Dec 2006 21:16:01

The two arithmetic operators may also be used as compound assignment operators. This is achieved by appending an equals sign (=) to the operator. The following examples demonstrate this after using the Parse method to create a TimeSpan value. This method operates in a similar manner to the DateTime’s Parse method.

DateTime theDate = DateTime.Parse("2 Jan 2007 20:15:00");

// Add one hour, one minute and one second
theDate += TimeSpan.Parse("01:01:01");        // theDate = 2 Jan 2007 21:16:01

// Subtract twenty-five days
theDate -= TimeSpan.Parse("25.00:00:00");     // theDate = 8 Dec 2006 21:16:01

Add Methods

The Add methods allow more control over the processing of DateTime values. They do not require the use of a TimeSpan value, which means that adjustment of greater values for individual elements of a date or time value are possible. For example, it is simple to add more than sixty minutes to a DateTime value without calculating how many hours or days are involved in the calculation.

The first Add method to look at is the Add method itself. This provides the same functionality as the addition operator, simply increasing a DateTime value using a duration held as a TimeSpan.

DateTime theDate = DateTime.Parse("2 Jan 2007 20:15:00");
TimeSpan duration = TimeSpan.Parse("1.01:01:01");

theDate = theDate.Add(duration);              // theDate = 3 Jan 2007 21:16:01

The Add method provides no greater control over the calculation than the simple arithmetic operators. To gain flexibility, we can use methods for adding a number of years, months, etc. There is even an Add method to allow us to add ticks; a single tick being one ten-millionth of a second. These methods are all named Add followed by the type of DateTime element to be added. Values may also be subtracted by passing a negative parameter to the appropriate Add method. Each of the methods accepts a single double-precision number as a parameter except for AddTicks, which requires a long integer parameter.

DateTime theDate = DateTime.Parse("2 Jan 2007");

theDate = theDate.AddYears(1);                // theDate = 2 Jan 2008
theDate = theDate.AddMonths(2);               // theDate = 2 Mar 2008
theDate = theDate.AddDays(1.5);               // theDate = 3 Mar 2008 12:00:00
theDate = theDate.AddHours(-6);               // theDate = 2 Mar 2008 06:00:00
theDate = theDate.AddMinutes(150);            // theDate = 2 Mar 2008 08:30:00
theDate = theDate.AddSeconds(10.5);           // theDate = 2 Mar 2008 08:30:10.5
theDate = theDate.AddMilliseconds(499);       // theDate = 2 Mar 2008 08:30:10.999
theDate = theDate.AddTicks(10000);            // theDate = 2 Mar 2008 08:30:11

Subtract Method

The Subtract method provides similar function to the basic Add method. However, it can be used in two ways. Firstly, a TimeSpan may be subtracted from a DateTime to return a new DateTime. Secondly, one DateTime value may be subtracted from another resulting in a TimeSpan value that describes the difference between the two original values.

DateTime theDate = DateTime.Parse("2 Jan 2007");
TimeSpan subtract = TimeSpan.Parse("1.01:00:00");
DateTime startDate = DateTime.Parse("1 Jan 2007");
DateTime endDate = DateTime.Parse("2 Jan 2007 12:00:00");
TimeSpan diff;

// Subtract a TimeSpan from a DateTime
theDate = theDate.Subtract(subtract);         // theDate = 31 Dec 2006 23:00:00

// Find the difference between two dates
diff = endDate.Subtract(startDate);           // diff = 1.12:00:00

NB: In the above example the difference between the dates is positive because the DateTime being subtracted is earlier than that being subtracted from. If the reverse was true then the result would be negative.

UTC and Local DateTime Conversion

The DateTime structure allows the storage of both Co-ordinated Universal Time (UTC) and local values for times. The UTC value is ideal when it is necessary to track the exact time and ordering of events that may span time zones or daylight savings time changes. However, the local time value is preferred when displaying a time to a user. Luckily, the DateTime structure provides two methods that allow conversion between the two standards to be performed using the user’s local system settings to achieve the correct results. These methods are ToUniversalTime and ToLocalTime. For the following example, Rangoon has been selected in the Window’s time zone settings for its interesting six and a half hour offset from UTC.

DateTime localDate = DateTime.Parse("1 Jul 2007 12:00");

DateTime utcDate = localDate.ToUniversalTime();     // 1 Jul 2007 05:30
DateTime rangoon = utcDate.ToLocalTime();           // 1 Jul 2007 12:00

It is important to note that the DateTime structure itself does not have any knowledge of which type of date (UTC or local) is held. The two methods simply add or subtract the time zone offset as instructed. If used incorrectly, the methods return invalid results.

<!–
google_ad_client = “pub-3760328251384501″;
/* Inline Rectangle */
google_ad_slot = “1524970617″;
google_ad_width = 300;
google_ad_height = 250;
//–>

window.google_render_ad();

Conversion from DateTime to String

Once a DateTime value has been calculated it is usually necessary to display it to the user. Ideally, the date and time will be displayed in a format recognisable by the user and dependant upon the preferences that they have expressed, either in the software’s configuration or, more usually, in their system configuration. The DateTime structure provides several methods to achieve a conversion of date and time information to strings, which may then be used for display purposes.

Basic Conversions

When a Microsoft Windows operating system is installed on a personal computer, the user selects the region of the world where they live and this information is used to determine how dates and times are formatted. Some users may further customise their settings to achieve their preferences for long and short dates and times. As developers, we should honour their selections by ensuring that output from our programs uses these settings.

The DateTime structure provides four standard methods for converting DateTime values to strings. These allow the automatic generation of a string containing a long or short date or time. The names of the methods are ToLongDateString, ToShortDateString, ToLongTimeString and ToShortTimeString. None of the four functions requires the use of parameters.

NB: The following example assumes that the code is executing on a UK machine. The results in other countries or on customised systems may differ.

DateTime theDate = DateTime.Parse("3 Jan 2007 21:25:30");
string result;

result = theDate.ToLongDateString();          // result = "03 January 2007"
result = theDate.ToShortDateString();         // result = "03/01/2007"
result = theDate.ToLongTimeString();          // result = "21:25:30"
result = theDate.ToShortTimeString();         // result = "21:25"

Using ToString with Format Specifiers

The basic formatting provided by the four conversion methods described above is useful in many cases. For greater control, using the ToString method allows the programmer to specify a format specifier as a parameter. This format specifier permits the use of a wider range of date and time styles. It does, however, limit the user’s control over how they view an application’s output.

DateTime theDate = DateTime.Parse("3 Jan 2007 21:25:30");
string result;

result = theDate.ToString("d");       // result = "03/01/2007"
result = theDate.ToString("f");       // result = "03 January 2007 21:25"
result = theDate.ToString("y");       // result = "January 2007"

Available Format Specifiers

The full list of format specifiers for DateTime conversion is as follows:

Specifier	Description	Example
d	Short date format. This is equivalent to using ToShortDateString.	“03/01/2007″
D	Long date format. This is equivalent to using ToLongDateString.	“03 January 2007″
f	Date and time using long date and short time format.	“03 January 2007 21:25″
F	Date and time using long date and time format.	“03 January 2007 21:25:30″
g	Date and time using short date and time format.	“03/01/2007 21:25″
G	Date and time using short date and long time format.	“03/01/2007 21:25:30″
m	Day and month only.	“03 January”
r	Date and time in standard Greenwich Mean Time (GMT) format.	“Wed, 03 Jan 2007 21:25:30 GMT”
s	Sortable date and time format. The date elements start at the highest magnitude (year) and reduce along the string to the smallest magnitude (seconds).	“2007-01-03T21:25:30″
t	Short time format. This is equivalent to using ToShortTimeString.	“21:25″
T	Long time format. This is equivalent to using ToLongTimeString.	“21:25:30″
u	Short format, sortable co-ordinated universal time.	“2007-01-03 21:25:30Z”
U	Long format date and time.	“03 January 2007 17:25:30″
y	Month and year only.	“January 2007″

Using Picture Formats for Custom Formatting

The standard format specifiers are useful in most cases whilst still maintaining the correct style of date and time for the user’s location. On some occasions however, it is necessary to have complete control over the positioning and styling of each element of date and time information. For this, picture formats must be used. These allow an exact format to be created using any combination of elements from a DateTime.

DateTime theDate = DateTime.Parse("3 Jan 2007 21:25:30");
string result;

result = theDate.ToString("d-MM-yy");       // result = "3-01-07"
result = theDate.ToString("HH:mm");         // result = "21:25"
result = theDate.ToString("h:mm tt");       // result = "9:25 PM"

Available Picture Formatting Codes

The above example shows several formats and the results. The full list of available formatting codes is as follows:

Specifier	Description	Examples
y	One-digit year. If the year cannot be specified in one digit then two digits are used automatically.	“7″ “95″
yy	Two-digit year with leading zeroes if required.	“07″
yyyy	Full four-digit year.	“2007″
g or gg	Indicator of Anno Domini (AD).	“A.D.”
M	One-digit month number. If the month cannot be specified in one digit then two digits are used automatically.	“1″ “12″
MM	Two-digit month number with leading zeroes if required.	“01″
MMM	Three letter month abbreviation.	“Jan”
MMMM	Month name.	“January”
d	One-digit day number. If the day cannot be specified in one digit then two digits are used automatically.	“3″ “31″
dd	Two-digit day number with leading zeroes if required.	“03″
ddd	Three letter day name abbreviation.	“Wed”
dddd	Day name.	“Wednesday”
h	One-digit hour using the twelve hour clock. If the hour cannot be specified in one digit then two digits are used automatically.	“9″ “12″
hh	Two-digit hour using the twelve hour clock with leading zeroes if required.	“09″
H	One-digit hour using the twenty four hour clock. If the hour cannot be specified in one digit then two digits are used automatically.	“1″ “21″
HH	Two-digit hour using the twenty four hour clock with leading zeroes if required.	“09″
t	Single letter indicator of AM or PM, generally for use with twelve hour clock values.	“A” “P”
tt	Two letter indicator of AM or PM, generally for use with twelve hour clock values.	“AM” “PM”
m	One-digit minute. If the minute cannot be specified in one digit then two digits are used automatically.	“1″ “15″
mm	Two-digit minute with leading zeroes if required.	“01″
s	One-digit second. If the second cannot be specified in one digit then two digits are used automatically.	“1″ “59″
ss	Two-digit second with leading zeroes if required.	“01″
f	Fraction of a second. Up to seven f’s can be included to determine the number of decimal places to display.	“0″ “0000000″
z	One-digit time zone offset indicating the difference in hours between local time and UTC time. If the offset cannot be specified in one digit then two digits are used automatically.	“+6″ “-1″
zz	Two-digit time zone offset indicating the difference in hours between local time and UTC time with leading zeroes if required.	“+06″

Some of the formatting codes use the same letter as a format specifier. When used within a format string that is greater than one character in length this does not present a problem. If you need to use a single character picture format string, the letter must be preceded by a percentage sign (%) to indicate that the standard format specifier is not to be used.

DateTime theDate = DateTime.Parse("3 Jan 2007 21:25:30");
string result;

result = theDate.ToString("d");       // result = "03/01/2007"
result = theDate.ToString("%d");      // result = "3"

Using String.Format

In the article, C# String Generation with String.Format I described the use of the String.Format method for generating strings containing formatted numeric information. This method may also be used for including DateTime values in strings using the format specifiers described above.

Prev << DateTime Information Extraction

Next >> C# Arrays

Link to this Page