How do I insert a DateTime.Now into SQL

Hi There,
I am tearing my hair out. I cannot see how to insert a date into SQL server.

I have a datetime field in an SQL database.
I want to store a .NET DateTime type into the SQL field.

I have tried:
string dateTimeNow = DateTime.Now.ToString();
testTimeStop = SqlDateTime.Parse(dateTimeNow);

and because I thought it might need the SQL format.

string dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ffff");
testTimeStop = SqlDateTime.Parse(dateTimeNow);

In both cases I get an error:
"String was not recognized as a valid DateTime."

I have also tried a vast panoply of other ideas from the web. None of which have panned out.

I can't just store it as a varchar ( although I would like to!)


Any ideas / suggestions?

Thanks in advance
Richard

You can do this easily with a stored procedure. The example below calls a stored procedure named "[CGTest.SetTime]" with a DateTime parameter called "@DateTimeNow". The stored procedure inserts the current time into a table called CGTime.

Here's the C# code ( you'll need to fix up the connection string ):

Code:

---

using( SqlConnection cn = new SqlConnection( "Server=myMachineName;Trusted_Connection=true;database=myDatabase" ) )
{
        cn.Open( );
        using( SqlCommand cmd = new SqlCommand( "[CGTest.SetTime]", cn ) )
        {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                SqlParameterCollection sqlParams = cmd.Parameters;
                sqlParams.AddWithValue( "DateTimeNow", DateTime.Now );

                cmd.ExecuteNonQuery( );
        }
}

---

Sql Script for table:

Code:

---

USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CGTime](
        [TimeID] [int] IDENTITY(1,1) NOT NULL,
        [Current] [datetime] NOT NULL,
 CONSTRAINT [PK_CGTime] PRIMARY KEY CLUSTERED
(
        [TimeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

---

Sql Script for stored procedure:

Code:

---

USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:                Arjay
-- Create date: 2008-08-28
-- Description:        Test sproc to insert a datetime
-- =============================================
CREATE PROCEDURE [dbo].[CGTest.SetTime]
        @DateTimeNow DATETIME
AS
BEGIN
        SET NOCOUNT ON;

        INSERT INTO [dbo].[CGTime] ([Current])
                VALUES ( @DateTimeNow)
END

---

Thanks for the reply.
It is evening now so I'll try this at work tomorrow and let you know.

Cheers
Richard

That works well for me.

Thanks Arjay

I think the stored procedure approach might be overkill for the simple process of adding a datetime to the database. The reason it works is that he is adding it as a DateTime within that procedure, and not as a string as you were attempting. So, instead of

Code:

---

string dateTimeNow = DateTime.Now.ToString();

---

try

Code:

---

DateTime dateTimeNow = DateTime.Now;

---