Sql query to insert datetime 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 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

User · Answer

Management studio creates scripts like    insert table1  foodate  values CAST N 2012-06-18 10 34 09 000  AS DateTime

User · Answer

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

User · Answer

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

User · Answer

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  lt tableName gt   lt dateTime column gt   values coalesce   TRY CONVERT datetime   lt DateString  121   TRY CONVERT datetime   lt DateString gt    101   TRY CONVERT datetime   lt DateString gt   102   TRY CONVERT datetime    lt DateString gt   103

User · Answer

you need to add it like  insert into table1 date1  values  12-mar-2013

User · Answer

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

User · Answer

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  lt    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  gt   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

[sql] Sql query to insert datetime in SQL Server

Examples related to sql

Examples related to sql-server-2008

Examples related to datetime