177

I want to insert a datetime value into a table (SQL Server) using the SQL query below

insert into table1(approvaldate)values(18-06-12 10:34:09 AM);

But I get this Error msg:

Incorrect syntax near '10'.

I tried it with the quotes

insert into table1(approvaldate)values('18-06-12 10:34:09 AM');

I get this error message

Cannot convert varchar to datetime

Stephen Ostermiller
  • 23,933
  • 14
  • 88
  • 109
Shee
  • 1,875
  • 2
  • 14
  • 16

9 Answers9

297

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.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 1
    Thank you.. It works. Btw is it not possible to enter in DD-MM-YY format? – Shee Oct 18 '12 at 15:03
  • 1
    @RichardTheKiwi what does the `5` signify? – Shee Oct 18 '12 at 15:09
  • @Shee see added footer to answer – RichardTheKiwi Oct 18 '12 at 15:15
  • 7
    I stand corrected - if you're using `YYYYMMDD HH:MM:SS` (in the 24-hour format - the world format, anyone but the US uses this, not just the military....) **without** the dashes - it works, even for `British` language. If you use `YYYY-MM-DD HH:MM:SS` however, it fails - in that case (with the dashes), you **do need** to separate date and time with a `T` literal. – marc_s Oct 18 '12 at 15:16
  • 1
    @Shee Take a look at the [`SET DATEFORMAT` statement](https://msdn.microsoft.com/en-us/library/ms189491.aspx). This is the setting for an individual connection, but the setting can be specified server-wide. You can also specify [`SET LANGUAGE`](https://msdn.microsoft.com/en-us/library/ms174398.aspx) to control things like which character is used for the radix and such, and the date format will inherit from that (again, at the server or connection levels). It's just that the default is US English, and mdy is the US format. ymd with 4 year dates does always work, however. – Bacon Bits Jun 10 '15 at 17:07
  • How to do this in PostgreSql query ? I am using timestamp datatype. – Shesha Jun 30 '16 at 05:45
  • @Shesha The first query I provided works in PostgreSQL unchanged. FYI you can try queries on this site http://sqlfiddle.com/#!15/453e3/1 – RichardTheKiwi Jun 30 '16 at 22:01
35

A more language-independent choice for string literals is the international standard ISO 8601 format "YYYY-MM-DDThh:mm:ss". I used the SQL query below to test the format, and it does indeed work in all SQL languages in sys.syslanguages:

declare @sql nvarchar(4000)

declare @LangID smallint
declare @Alias sysname

declare @MaxLangID smallint
select @MaxLangID = max(langid) from sys.syslanguages

set @LangID = 0

while @LangID <= @MaxLangID
begin

    select @Alias = alias
    from sys.syslanguages
    where langid = @LangID

    if @Alias is not null
    begin

        begin try
            set @sql = N'declare @TestLang table (langdate datetime)
    set language ''' + @alias + N''';
    insert into @TestLang (langdate)
    values (''2012-06-18T10:34:09'')'
            print 'Testing ' + @Alias

            exec sp_executesql @sql
        end try
        begin catch
            print 'Error in language ' + @Alias
            print ERROR_MESSAGE()
        end catch
    end

    select @LangID = min(langid)
    from sys.syslanguages
    where langid > @LangID
end

According to the String Literal Date and Time Formats section in Microsoft TechNet, the standard ANSI Standard SQL date format "YYYY-MM-DD hh:mm:ss" is supposed to be "multi-language". However, using the same query, the ANSI format does not work in all SQL languages.

For example, in Danish, you will many errors like the following:

Error in language Danish The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

If you want to build a query in C# to run on SQL Server, and you need to pass a date in the ISO 8601 format, use the Sortable "s" format specifier:

string.Format("select convert(datetime2, '{0:s}'", DateTime.Now);
Paul Williams
  • 16,585
  • 5
  • 47
  • 82
  • 3
    Seems you can use `YYYYMMDD` for date only, and either `YYYY-MM-DDThh:mm:ss` (with dashes, and yes, a `T` between date and time portion!) or `YYYYMMDD HH:MM:SS` (no dashes, no separation literal) to be truly language independent... – marc_s Oct 18 '12 at 15:17
  • 1
    @marc_s: Thank you for the comment. I edited my answer to mention the ISO 8601 format (which is includes the T literal as you mentioned). – Paul Williams Dec 16 '13 at 17:18
  • insert into @TestLang (langdate) values ('2012-06-18T10:34:09') is the best answer. – MERT DOĞAN Mar 16 '17 at 21:36
  • This is the only one that works for my (shared) SQL server. YYYY-MM-DDThh:mm:ss gives the correct mnth/date; but without the 'T' (YYYY-MM-DD hh:mm:ss) the month and date are swapped (sometimes of course giving impossible values), Without the hyphens (YYYDDMM hh:mm:ss) also seems to work but I can't trust it, because it depends on the DB admin's language settings which may change. – quilkin Dec 06 '20 at 15:40
24

Management studio creates scripts like: 

insert table1 (foodate) values(CAST(N'2012-06-18 10:34:09.000' AS DateTime))
josliber
  • 43,891
  • 12
  • 98
  • 133
Eduardo Aguiar
  • 247
  • 2
  • 3
14

you need to add it like

insert into table1(date1) values('12-mar-2013');
laalto
  • 150,114
  • 66
  • 286
  • 303
nid
  • 141
  • 1
  • 2
5

No need to use convert. Simply list it as a quoted date in ISO 8601 format.
Like so:

select * from table1 where somedate between '2000/01/01' and '2099/12/31'

The separator needs to be a / and it needs to be surrounded by single ' quotes.

Johan
  • 74,508
  • 24
  • 191
  • 319
2

If you are storing values via any programming language

Here is an example in C#

To store date you have to convert it first and then store it

insert table1 (foodate)
   values (FooDate.ToString("MM/dd/yyyy"));

FooDate is datetime variable which contains your date in your format.

Pankaj
  • 49
  • 4
1

I encounter into a more generic problem: getting different (and not necessarily known) datetime formats and insert them into datetime column. I've solved it using this statement, which was finally became a scalar function (relevant for ODBC canonical, american, ANSI and british\franch date style - can be expanded):

insert into <tableName>(<dateTime column>) values(coalesce 
(TRY_CONVERT(datetime, <DateString, 121), TRY_CONVERT(datetime, <DateString>, 
101), TRY_CONVERT(datetime, <DateString>, 102), TRY_CONVERT(datetime, 
<DateString>, 103)))
Guy E
  • 1,775
  • 2
  • 27
  • 55
0

If the format of the date is as follows in sql : (datetime,null)

1

You can also use the "CAST" keyword

(CAST('2015-12-25 15:32:06.427' AS DateTime))

Just make sure that the date is in the correct format

David Cachia
  • 1
  • 1
0

You need to change month from number to word insert table1 (foodate) values('2012-JULY-18 10:34:09.000')

Chieu Luu
  • 1