CODEWITHSUNDEEP
sql-serverdatetimetype-conversionvarchar
Paul Karam
Paul Karam

Reputation: 4210

What is the default date style used by an instance of SQL Server when converting from varchar to datetime?

I've run this SQL query on two different servers:

declare @test as datetime='2020-05-06 00:00:00'
select Convert (nvarchar,@test)

The results were different on the two servers:

  1. May 6 2020 12:00AM
  2. Jun 5 2020 12:00AM

I know the reason behind this, when SQL Server is reading the string that I passed 2020-05-06 00:00:00 and converting it to DateTime in the declare statement, it's using the default date style.

Am I able to configure this default style, or in other words, how is default date style chosen in SQL Server when converting the varchar to datetime? Is it from the windows regional settings or some other configurations inside SQL Server?

Upvotes: 0

Views: 1038

Answers (1)

Thom A
Thom A

Reputation: 95554

It uses a style based on the language. Basically, for the date above, if you're American then the date will be read as yyyy-MM-dd hh:mm:ss, however, if use over languages, then it's be (stupidly) read as yyyy-dd-MM hh:mm:ss.

If you are using strings for dates (like your literal here) then aim to use an unambiguous format. In SQL Server, regardless of data type and language, those are yyyy-MM-ddThh:mm:ss.nnnnnnn and yyyyMMdd.

If you're convert to an (n)varchar, always use a style code (and a length for your varchar) for consistent results.

So, for your value, you can run the below to find out what the default conversion value would be for all the languages on your instance:

DECLARE Languages CURSOR FOR
SELECT alias
FROM sys.syslanguages;

DECLARE @Alias sysname,
        @SQL nvarchar(MAX);

CREATE TABLE #ConvertedDates (Alias sysname, dt datetime, converted nvarchar(25));

DECLARE @dt datetime = '2020-05-06T00:00:00'

OPEN Languages

FETCH NEXT FROM Languages
INTO @Alias;

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQL = N'SET LANGUAGE ' + QUOTENAME(@Alias) + N'; INSERT INTO #ConvertedDates(Alias,dt,converted) VALUES(N' + QUOTENAME(@Alias,'''') + ',@dt,CONVERT(nvarchar(25),@dt));';

    EXEC sys.sp_executesql @SQL, N'@dt datetime', @dt;

    FETCH NEXT FROM Languages
    INTO @Alias;

END;

CLOSE Languages;
DEALLOCATE Languages;

SELECT *
FROM #ConvertedDates;

DROP TABLE #ConvertedDates;

Yes, that is a Cursor. I wanted to ensure that each dynamic statement ran by itself, to ensure language was preserved for each conversion.

Upvotes: 2

Related Questions