how to insert datetime into the SQL Database table?

.net sql sql-server sql-server-2005 c#-3.0
Srihari picture Srihari · Mar 13, 2011 · Viewed 598.9k times · Source

How can I insert datetime into the SQL Database table ? Is there a way to insert this query through the insert command in C# / .NET?

Answer

Thomas picture Thomas · Mar 13, 2011

DateTime values should be inserted as if they are strings surrounded by single quotes:

'20100301'

SQL Server allows for many accepted date formats and it should be the case that most development libraries provide a series of classes or functions to insert datetime values properly. However, if you are doing it manually, it is important to distinguish the date format using DateFormat and to use generalized format:

Set DateFormat MDY --indicates the general format is Month Day Year

Insert Table( DateTImeCol )
Values( '2011-03-12' )

By setting the dateformat, SQL Server now assumes that my format is YYYY-MM-DD instead of YYYY-DD-MM.

SET DATEFORMAT

SQL Server also recognizes a generic format that is always interpreted the same way: YYYYMMDD e.g. 20110312.

If you are asking how to insert the current date and time using T-SQL, then I would recommend using the keyword CURRENT_TIMESTAMP. For example:

Insert Table( DateTimeCol )
Values( CURRENT_TIMESTAMP )

Related questions

How to Troubleshoot Intermittent SQL Timeout Errors

We've been having a few instances per day where we get a slew of SQL Timeout errors from multiple applications (System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is …

Read More
How can you name the Dataset's Tables you return in a stored proc?

I've got the following stored procedure Create procedure psfoo () AS select * from tbA select * from tbB I'm then accessing the data this way : Sql Command mySqlCommand = new SqlCommand("psfoo" , DbConnection) DataSet ds = new DataSet(); mySqlCommand.CommandType = CommandType.StoredProcedure; SqlDataAdapter mySqlDataAdapter = …

Read More
How to check for database availability

I have the following code to test DB connection, it runs periodically to test for DB availability: private bool CheckDbConn() { SqlConnection conn = null; bool result = true; try { conn = DBConnection.getNewCon(); ConnectionState conState = conn.State; if (conState == ConnectionState.Closed || conState == ConnectionState.…

Read More