CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    28

    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

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    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

  3. #3
    Join Date
    Oct 2005
    Posts
    28

    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

  4. #4
    Join Date
    Oct 2005
    Posts
    28

    Re: How do I insert a DateTime.Now into SQL

    That works well for me.

    Thanks Arjay

  5. #5
    Join Date
    Aug 2007
    Posts
    50

    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
  •  





Click Here to Expand Forum to Full Width

Featured