Sql Query to Insert Datetime in Sql Server

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

Management studio creates scripts like:

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

you need to add it like

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

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.

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.

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

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