76

How can I insert datetime into the SQL Database table ? Is there a way to insert this query through the insert command in C# / .NET?

Alexis Pigeon
  • 7,423
  • 11
  • 39
  • 44
Srihari
  • 2,509
  • 5
  • 30
  • 34
  • 1
    In this discussion is solved for datetime in SQL server http://stackoverflow.com/a/12957690/2120484 – oaamados Jan 02 '14 at 17:04

5 Answers5

114

DateTime values should be inserted as if they are strings surrounded by single quotes:

'20100301'

SQL Server allows for many accepted date formats and it should be the case that most development libraries provide a series of classes or functions to insert datetime values properly. However, if you are doing it manually, it is important to distinguish the date format using DateFormat and to use generalized format:

Set DateFormat MDY --indicates the general format is Month Day Year

Insert Table( DateTImeCol )
Values( '2011-03-12' )

By setting the dateformat, SQL Server now assumes that my format is YYYY-MM-DD instead of YYYY-DD-MM.

SET DATEFORMAT

SQL Server also recognizes a generic format that is always interpreted the same way: YYYYMMDD e.g. 20110312.

If you are asking how to insert the current date and time using T-SQL, then I would recommend using the keyword CURRENT_TIMESTAMP. For example:

Insert Table( DateTimeCol )
Values( CURRENT_TIMESTAMP )
Thomas
  • 63,911
  • 12
  • 95
  • 141
  • 1
    Is that correct? You `Set DateFormat MDY`, but later you say that SQL Server now assumes my format is `YYY-MM-DD`. Why doesn't SQL Server expect your format to match the `Set DateFormat` and look for `MM-DD-YYYY`? I'm not saying it's wrong (I don't know), but it seems counter-intuative. – JerryOL Mar 13 '11 at 21:21
  • 3
    @JerryOL - The `Set DateFormat MDY` only determines how SQL interprets the the order of month and day, not the exact format. You can try it for yourself. If you call `Set DateFormat DMY`, `2011-03-12` is Dec 3rd. If you call `Set DateFormat MDY`, that same date is March 12th. – Thomas Mar 13 '11 at 22:41
  • 2
    @JerryOL - Btw, I could have also used `YMD` or `YDM` and achieved the same effect however I find that those formats are only useful if you are not passing the four digit year. – Thomas Mar 13 '11 at 22:43
15

You will need to have a datetime column in a table. Then you can do an insert like the following to insert the current date:

INSERT INTO MyTable (MyDate) Values (GetDate())

If it is not today's date then you should be able to use a string and specify the date format:

INSERT INTO MyTable (MyDate) Values (Convert(DateTime,'19820626',112)) --6/26/1982

You do not always need to convert the string either, often you can just do something like:

INSERT INTO MyTable (MyDate) Values ('06/26/1982')

And SQL Server will figure it out for you.

Rob
  • 2,466
  • 3
  • 22
  • 40
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
8

if you got actuall time in mind GETDATE() would be the function what you looking for

bensiu
  • 24,660
  • 56
  • 77
  • 117
0

DateTime values should be inserted as if they are strings surrounded by single quotes '20201231' but in many cases they need to be casted explicitly to datetime CAST(N'20201231' AS DATETIME) to avoid bad execution plans with CONVERSION_IMPLICIT warnings that affect negatively the performance. Hier is an example:

CREATE TABLE dbo.T(D DATETIME)

--wrong way
INSERT INTO dbo.T (D) VALUES ('20201231'), ('20201231')

Warnings in the execution plan

--better way
INSERT INTO dbo.T (D) VALUES (CAST(N'20201231' AS DATETIME)), (CAST(N'20201231' AS DATETIME))

No warnings

Alpan
  • 51
  • 1
  • 6
-1
myConn.Execute "INSERT INTO DayTr (dtID, DTSuID, DTDaTi, DTGrKg) VALUES (" & Val(txtTrNo) & "," & Val(txtCID) & ", '" & Format(txtTrDate, "yyyy-mm-dd") & "' ," & Val(Format(txtGross, "######0.00")) & ")"

Done in vb with all text type variables.

sgarizvi
  • 16,623
  • 9
  • 64
  • 98
Ranatunge
  • 9