CODEWITHSUNDEEP
mysqlsql-serverdatetimesql-insert
Denoteone
Denoteone

Reputation: 4055

MSSQL format date and time during INSERT

How do I format the Date and Time when inserting it into my MSSQL Query?

I know this has been asked before but I am not sure why I am having so much trouble understanding how to formatting the current date and time when INSERTING it to my database.

Originally the following INSERT Query worked but now I want to add the current date and time with the minutes as the last characters. No seconds or Millaseconds

INSERT INTO Form_Submission (ID,Submission_Date,Status)
VALUES  ('23', CURRENT_TIMESTAMP, 'Open');

The formatting I am looking for is: 2015-04-08 13:42

Upvotes: 0

Views: 1327

Answers (3)

veljasije
veljasije

Reputation: 7092

Maybe you can use FORMAT() T-SQL function:

INSERT INTO     Form_Submission (ID,Submission_Date,Status)
VALUES          ('23', FORMAT(CURRENT_TIMESTAMP, 'yyyy-MM-dd hh:mm'), 'Open')

Upvotes: 0

Tom Chantler
Tom Chantler

Reputation: 14941

If you are trying to insert into a DATETIME or SMALLDATETIME field then this will work:

CONVERT(SMALLDATETIME, GETDATE())

or CONVERT(SMALLDATETIME, CURRENT_TIMESTAMP) if you want.

So your code is:

INSERT INTO     Form_Submission (ID,Submission_Date,Status)
VALUES          ('23', CONVERT(SMALLDATETIME, GETDATE()), 'Open')

The point is that you are not supposed to format dates and times when inserting into the database, you should only format them on the way out (when displaying). SMALLDATETIME offers the level of granularity you seem to want (accurate to the nearest minute).

See MSDN documentation here: GETDATE and SMALLDATETIME

Upvotes: 0

ughai
ughai

Reputation: 9890

you can use LEFT and CONVERT with style 120 like this

INSERT INTO     Form_Submission (ID,Submission_Date,Status)
 VALUES          ('23', CONVERT(DATETIME,LEFT(CONVERT(VARCHAR(40),CURRENT_TIMESTAMP,120),16)), 'Open')

For more details about different styles used with Convert refer here

Upvotes: 2

Related Questions