How do I insert a DateTime.Now into SQL

[Richard210363]

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

[Arjay]

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 ):

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:

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:

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

[Richard210363]

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

Cheers
Richard

[Richard210363]

That works well for me.

Thanks Arjay

[drowned]

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

string dateTimeNow = DateTime.Now.ToString();

try

DateTime dateTimeNow = DateTime.Now;