How to insert time only from datetime in C# and sql

siva nathan

i have a grid like below i want to insert a time only from a datetime

intime	outtime
01-01-2017 08:45:12	01-01-2017 20:00:12
01-02-2017 08:45:12	01-02-2017 18:45:12

my expected result is i want to store only time in sql column

08:45:12	20:00:12
08:45:12	18:45:12

pls explain in both C# and sql

what kind of datatype i have to use to store time only in sql column

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
I would suggest to let it be handled by server itself.
Use Time as the datatype of column.
Try this code.
CREATE TABLE #tempX(data TIME)
DECLARE @somedate DATETIME = GETDATE();
INSERT INTO #tempX
        ( data )
VALUES  ( @somedate
          )
SELECT * FROM #tempX
DROP TABLE #tempX
-1
Rafnas T P9y ago
in sql you can use datatype as time
and in front end you can use any time control like datetime picker
0
Nilesh Shah9y ago
create test table:
CREATE TABLE [dbo].[TimeOnlyTest](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TimeOnly] [time](7) NOT NULL,
CONSTRAINT [PK_TimeOnlyTest] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
create SP:
CREATE PROCEDURE [dbo].[SP_TimeOnlyTest]
    @timeonly nvarchar(8)
AS
BEGIN
    insert into TimeOnlyTest(TimeOnly) values (CONVERT(TIME, @timeonly))
END

GO
call from C# code:
SqlConnection con = new SqlConnection("Data Source=localhost;initial catalog= LocalTest;Integrated Security=True;");
            con.Open();

            SqlCommand command = new SqlCommand("SP_TimeOnlyTest", con);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@timeonly", SqlDbType.NVarChar, 0, "timeonly"));

            DateTime thisTime = DateTime.Now;
            command.Parameters[0].Value = thisTime.Hour.ToString() + ":" + thisTime.Minute.ToString() + ":" + thisTime.Second.ToString();

            int i = command.ExecuteNonQuery();
            con.Close();
0
Ayan Roy9y ago
You can change the date format using C# as well as SQL .
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
namespace Rextester
{
public class Program
{
public static void Main(string[] args)
{
DateTime dt = DateTime.Now;
string time = dt.ToString("hh:mm:ss tt");
Console.WriteLine(time);
}
}
}

and if you want to get the time from SQL server Then You can use the Date function .
SELECT CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinute .
I think this will help you :).
+1
Amit Gupta9y ago
Yes, you can..Read the reference for more information
0
siva nathan9y ago
im using sql server2012,
how to achieve that task
0
Amit Gupta9y ago
If you are using SQL 2008 or newer, you can use TIME Datatype to store only time

Reference
https://msdn.microsoft.com/en-us/library/bb677243.aspx
0