Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sql query to insert datetime in SQL Server

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.


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.