SQL Query to Insert Datetime in SQL Server

SQL query to insert datetime in SQL Server

You will want to use the YYYYMMDD for unambiguous date determination in SQL Server.

insert into table1(approvaldate)values('20120618 10:34:09 AM');

If you are married to the dd-mm-yy hh:mm:ss xm format, you will need to use CONVERT with the specific style.

insert into table1 (approvaldate)
       values (convert(datetime,'18-06-12 10:34:09 PM',5));

5 here is the style for Italian dates. Well, not just Italians, but that's the culture it's attributed to in Books Online.

SQL Server : inserting datetime into table

The only truly safe formats for date/time literals in SQL Server, at least for datetime and smalldatetime, are: YYYYMMDD and YYYY-MM-DDThh:mm:ss[.nnn]

• Bad habits to kick : mis-handling date / range queries - Aaron Bertrand

---

You could probably get away with just setting set dateformat dmy before your insert though.

• set dateformat

rextester demo using set dateformat dmy;: http://rextester.com/NUQM21818

how to insert datetime into the SQL Database table?

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 )

Inserting datetime value into sql server table column

Assuming the situation is as you describe

CREATE TABLE T
(
S SMALLDATETIME NULL
)

INSERT INTO T 
VALUES('2013-08-30 19:05:00')

SELECT *
FROM T /*Returns NULL*/

There are only two ways I can think of that this can happen.

1) That is an ambiguous datetime format. Under the wrong session options this won't cast correctly and if you have some additional options OFF it will return NULL rather than raise an error (e.g.)

SET LANGUAGE Italian;
SET ansi_warnings OFF;
SET arithabort OFF; 

INSERT INTO T 
VALUES('2013-08-30 19:05:00')

SELECT *
FROM T /*NULL inserted*/

2) You may have missed the column out in an INSTEAD OF trigger, or have an AFTER trigger that actually sets the value back to NULL.

How to insert Datetime with offset in SQL server?

The correct data type is datetimeoffset -- although datetime and datetime2 would also work (assuming the values are all in the same time zone). SQL Server stores date/times using an internal format.

create table t (ts datetimeoffset);

insert into t (ts) values ('2020-06-16T13:41:36.000Z');

select * from t;

This returns:

ts
2020-06-16 13:41:36.0000000

This is equivalent to your value but formatted using an arbitrary format. If you want to control the format, then you need to convert the value to a string. One method uses convert() with option 127:

select convert(varchar(255), t.ts, 127) from t

format() provides more flexibility.

You can also add this logic into the table definition:

alter table t add ts_iso8601 as (convert(varchar(255), t.ts, 127));

Here is a db<>fiddle.

How to insert datetime like this format 'MM/dd/yyyy hh:mm:ss'?

Use this

Assuming the date column is date_column, you can use convert while displaying the date_column in various formats. Read the attached reference for various formats.

SELECT CONVERT(VARCHAR(10), date_column, 110) AS [MM-DD-YYYY] from tablename;

EDIT: Corrected the date format from Italian to US as I misread the question earlier.

http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx

I would like to insert datetime into SQL table as 13 digit number by sql query

If we assume 1536346340276 doesn't actually translate to 2014-12-31 15:17:24.736, but that the 13 digit number is simply an example of the data currently in the table and that the date provided is an example of how the input format will be, it looks like it could actually be Unix time

SELECT DATEADD(second, 1536346340276/1000 ,'1970/1/1')          -- is 2018-09-07 18:52:20.000
SELECT DATEDIFF_BIG(MILLISECOND,{d '1970-01-01'}, getdate()), GETDATE()     -- is 1537195014053 at 2018-09-17 14:36:54.053

If you're on SQL Server 2016 or higher, the new insert would look like:

INSERT INTO Test (id, assignedDate)
VALUES (1, (SELECT DATEDIFF_BIG(MILLISECOND,{d '1970-01-01'}, getdate())))

---

Related Topics