Date format problem

1

Hello,
in one of my views, I have a select with a where like this:

WHERE DataPub BETWEEN '20200720' AND '20200727'

but I've noticed that some servers has differente international settings,
so the date of 20 July 2020 could be rendered as

'20200720'

or

'20202007'

How can I avoid this problem?

Thank you in advance.

Luigi

2

by getting all into the same format and then comparing !!

3

are these servers within the same organization or servers under different organizations you work for?

4

get all into same format ( or what ever format you want )

using
CAST
or
CONVERT

and compare

1 Like
5

What is the datatype of the column?

If the column is date or datetime, you won't have any problems. The strings '20200720' and '20200727' will always be interpreted correctly as ymd.

If the column is char/varchar (bad idea!), and its format can change (very bad idea!), then you will have to adjust the code to make sure the format of the literal value matches the column value.

Incorrect. For dates/datetimes, SQL will always interpret 2020nnnn as ymd, never ydm.

1 Like
6

Different organizations with different time-zones.
L

7

That's interesting!
Columns are Date type.
So YYYYMMDD will be interpreted with same kind.
Thank you Scott.

L.

8

Good choice.
Thank you Harish.
L.

9

Just to add my 2 cents in support of what the others have said.

In SQL Server, @ScottPletcher s correct. When associated with a temporal datatype (DATETIME, SMALLDATETIME, DATE, DATETIME2), 'YYYYMMDD' is strictly ISO all the way and won't change based on language settings.

Just to explicitly state it so there is no mistake, that is patently NOT true with 'YYYY-MM-DD' string formats (even though it should be because it's also listed as ISO). For example, if you language is set to 'French", such strings will be interpreted as "YYYY-DD-MM".

That also makes @harishgg1 correct... you have to get everyone to use the 'YYYYMMDD' format in code literals and in data transfers (or SQL Native Format BCP Files between SQL Servers, which is not readable by normal humans).

10

This is only true for datetime and smalldatetime data types - for date and datetime2 it will always be interpreted as YYYY-MM-DD regardless of language. With that said - using YYYYMMDD works across all date data types and should be the default for any situation where language could be a factor.

1 Like
11

Thanks for the clarification, Jeff. I totally agree with the premise of defaulting to YYYYMMDD, as well.

12

As a bit of a sidebar, it's pretty amazing how many languages deviate from the YYYY-MM-DD format for DATETIME and SMALLDATETIME formats even as late as SQL Server 2017. I don't know about 2019 because I've not downloaded it yet.

Here's some code that demonstrates what I and @jeffw8713 and I are talking about (if you want to run the code to see)...

--===== Environmental Presets
    SET NOCOUNT ON;
    SET LANGUAGE 'English';

--===== Drop the results table to make reruns in SSMS easier.
   DROP TABLE IF EXISTS #Results
;
GO
--===== Create the test table.
CREATE TABLE #Results
        (
         DateTime       DATETIME        NOT NULL
        ,SmallDateTime  SMALLDATETIME   NOT NULL
        ,Date           DATE            NOT NULL
        ,DateTime2      DATETIME2(7)    NOT NULL
        ,Alias          NVARCHAR(256)   NOT NULL
        ,Conversion     VARCHAR(7)      NOT NULL
        )
;
--===== Brute force a PK using an index.
     -- We don't want to use a CONSTRAINT on a Temp Table.
 CREATE UNIQUE CLUSTERED INDEX PK_#Results ON #Results (Alias,Conversion)
;
GO
--===== Declare and preset some obviously named variables.
DECLARE  @DateString    NCHAR(10)       = N'2020-07-12' --YYYY-MM-DD intended
        ,@SQL           NVARCHAR(MAX)   = ''
;
--===== Create the test SQL.
     -- It creates one of the following for each language alias.
     -- Basically, it populates the results table using the various default conversions
     -- of @DateString.
 SELECT @SQL += REPLACE(REPLACE(REPLACE(N'
    SET LANGUAGE <<Alias>>
;
 INSERT INTO #Results WITH (TABLOCK)
        (
         DateTime     
        ,SmallDateTime
        ,Date         
        ,DateTime2    
        ,Alias        
        ,Conversion   
        )
 SELECT  DateTime       = CAST(<<@DateString>> AS DATETIME     )
        ,SmallDateTime  = CAST(<<@DateString>> AS SMALLDATETIME)
        ,Date           = CAST(<<@DateString>> AS DATE         )
        ,DateTime2      = CAST(<<@DateString>> AS DATETIME2    )
        ,Alias          = <<Alias>>
        ,Conversion     = "CAST"
  UNION
 SELECT  DateTime       = CONVERT(DATETIME     ,<<@DateString>>)
        ,SmallDateTime  = CONVERT(SMALLDATETIME,<<@DateString>>)
        ,Date           = CONVERT(DATE         ,<<@DateString>>)
        ,DateTime2      = CONVERT(DATETIME2    ,<<@DateString>>)
        ,Alias          = <<Alias>>
        ,Conversion     = "CONVERT"
;'      --Other end of REPLACEs
        ,'"','''')
        ,'<<Alias>>',QUOTENAME(Alias,''''))
        ,'<<@DateString>>',QUOTENAME(@DateString,''''))
   FROM sys.syslanguages
  ORDER BY Alias
;
--===== Run the dynamic SQL we created
--PRINT @SQL;
   EXEC (@SQL)
;
--===== Make sure we're back in the English language
    SET LANGUAGE 'English'
;
--===== Let's see what is "broken" and what is not.
 SELECT *
        ,IsDateTimeBroken       = IIF(DateTime      <> @DateString,'Yes','')
        ,IsSmallDateTimeBroken  = IIF(SmallDateTime <> @DateString,'Yes','')
        ,IsDateBroken           = IIF(Date          <> @DateString,'Yes','')
        ,IsDateTime2Broken      = IIF(DateTime2     <> @DateString,'Yes','')
   FROM #Results
  ORDER BY Alias,Conversion
;
1 Like
13

It is quite interesting to see which languages are not broken when using YYYY-MM-DD format - some of which are quite unexpected.

14

I was a bit gobsmacked at that, as well.