Sql query to insert date and time in SQL Server

Question

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 an error message. Incorrect syntax near '10'.

I tried it with quotes

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

I get this error message Cannot convert varchar to datetime

Kind help! Thank.

Answer 1

You want to use YYYYMMDD to uniquely determine the date in SQL Server.

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

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

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

5 here is the style of Italian dates. Well, not only Italians, but also the culture that she attributed to online books .

Answer 2

See String date and time formats in Microsoft TechNet.

You can use SQL Standard SQL date format. According to the link above, it is referred to as "multilingual":

 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 the date format in all SQL languages ​​defined 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-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 get many errors, for example:

Danish error Converting a varchar data type to a datetime data type resulted in a value out of range.

A more language-independent choice for string literals is the international standard ISO 8601 . This format is very similar to the ANSI standard, with the exception of the letter "T" between date and time:

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

I tested this and it really works in all SQL languages.

Answer 3

Management Studio creates such scripts as:

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

Answer 4

you need to add it as

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

Answer 5

No need to use convert. Just list it as a quoted date in ISO 8601 format.
For example:

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

The separator must be / and must be surrounded by single quotes. '

Answer 6

If you save values ​​through any programming language

Here is an example in C #

To save a date, you must first convert it and then save it

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

FooDate is a datetime variable that contains your date in your format.

Answer 7

I am facing a more general problem: I get different (and not necessarily known) datetime formats and paste them into the datetime column. I solved this with this statement, which finally became a scalar function (related to the canonical, American, American, British style of the franchise date and extension):

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