-
August 28th, 2008, 11:06 AM
#1
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
-
August 28th, 2008, 12:35 PM
#2
Re: How do I insert a DateTime.Now into SQL
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
-
August 28th, 2008, 01:10 PM
#3
Re: How do I insert a DateTime.Now into SQL
Thanks for the reply.
It is evening now so I'll try this at work tomorrow and let you know.
Cheers
Richard
-
August 29th, 2008, 10:12 AM
#4
Re: How do I insert a DateTime.Now into SQL
That works well for me.
Thanks Arjay
-
August 31st, 2008, 05:39 AM
#5
Re: How do I insert a DateTime.Now into SQL
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|