在Sql Server中插入datetime的Sql查询。

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

我想使用下面的SQL查询向表(SQL Server)插入一个datetime值

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

But I get this Error msg. Incorrect syntax near '10'.

但是我得到了这个错误msg。不正确的语法“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

我得到这个错误消息不能将varchar转换为datetime

Kindly help! Thanks.

请帮助!谢谢。

7 个解决方案

#1

154

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

您将希望在SQL Server中使用YYYYMMDD进行明确的日期确定。

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.

如果您使用的是dd-mm-yy hh:mm:ss xm格式，则需要使用特定样式的CONVERT。

insert 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.

这是意大利式约会的样式。不只是意大利人，这也是网络图书的文化。

#2

See the String Literal Date and Time Formats section in Microsoft TechNet.

请参阅Microsoft TechNet中的字符串文字日期和时间格式部分。

You could use the standard ANSI Standard SQL date format. According to the link above, it is labeled as "multi-language":

您可以使用标准的ANSI标准SQL日期格式。根据上面的链接，它被标记为“多语言”:

insert into table1(approvaldate) values ('2012-06-18 10:34:09')

However, this will not work in all languages. For example, here is a quick script that uses dynamic SQL to test a date format in all the SQL languages defined in sys.syslanguages:

然而，这并不适用于所有的语言。例如，这里有一个快速脚本，它使用动态SQL测试syss .syslanguage中定义的所有SQL语言的日期格式:

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-18 10: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

If you run this script, 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.

丹麦语错误将varchar数据类型转换为datetime数据类型会导致值超出范围。

A more language-independent choice for string literals is the international standard ISO 8601 format. This format is very similar to the ANSI standard except for a "T" literal between the date and time:

对于字符串文字来说，更独立于语言的选择是国际标准的ISO 8601格式。这种格式与ANSI标准非常相似，除了日期和时间之间有一个“T”字:

insert into @TestLang (langdate) values ('2012-06-18T10:34:09')

I tested this, and it does indeed work in all SQL languages.

我对它进行了测试，它确实适用于所有SQL语言。

#3

Management studio creates scripts like:

Management studio创建如下脚本:

insert table1 (foodate) values(CAST(N'2012-06-18 10:34:09.000' AS DateTime))

#4

you need to add it like

你需要加上它。

insert into table1(date1) values('12-mar-2013');

#5

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

不需要使用转换。只需以ISO 8601格式列出引用日期。像这样:

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.

分隔符需要是a /，并且它需要被单引号包围。

#6

If you are storing values via any programming language

如果您正在通过任何编程语言存储值

Here is an example in C#

下面是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.

FooDate是datetime变量，它以您的格式包含您的日期。

#7

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):

我遇到了一个更一般的问题:获取不同(不一定已知)的datetime格式，并将它们插入到datetime列中。我已经使用这个语句解决了它，它最终变成了一个标量函数(与ODBC canonical、american、ANSI和british\franch date样式相关):

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)))

#1

154