Sql query to insert datetime in SQL Server
SqlSql Server-2008DatetimeSql Problem Overview
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
Kindly help! Thanks.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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);
Solution 3 - Sql
Management studio creates scripts like:
insert table1 (foodate) values(CAST(N'2012-06-18 10:34:09.000' AS DateTime))
Solution 4 - Sql
you need to add it like
insert into table1(date1) values('12-mar-2013');
Solution 5 - Sql
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.
Solution 6 - Sql
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.
Solution 7 - Sql
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)))
Solution 8 - Sql
If the format of the date is as follows in sql : (datetime,null)
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