Date format changed from YYYYMMDD to YYYYDDMM if the month and date is below 12

Flaviana Ilao 0 Reputation points
2023-04-04T03:38:44.7366667+00:00

some of the records inserted in Database is incorrect while some records are inserted with YYYYMMDD format correctly. Noticed the date and month swapped if the date and month is below 12.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,879 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 41,101 Reputation points
    2023-04-04T06:55:50.54+00:00

    are inserted with YYYYMMDD format correctly.

    Data of type date don't have a format within the database, they are stored as some binary. It's always the client, with formats the date value, as it's best respecting the reginal settings of the user. But you haven't provide any detail about how you load data, which client to show the data etc.


  2. Erland Sommarskog 102.3K Reputation points
    2023-04-04T22:16:38.4733333+00:00

    The SQL Server version has nothing to do with this. But the dateformat setting has.

    CREATE TABLE #temp (i int, d datetime)
    go
    SET DATEFORMAT dmy
    go
    INSERT #temp(i, d) VALUES(1, '2022/02/07')
    go
    SET DATEFORMAT ymd
    go
    INSERT #temp(i, d) VALUES(2, '2022/02/07')
    go
    SET DATEFORMAT mdy
    go
    go
    INSERT #temp(i, d) VALUES(3, '2022/02/07')
    go
    SELECT i, d FROM #temp
    go
    DROP TABLE #temp
    
    

    The best way to avoid this is to use a safe format, YYYYMMDD (without delimiters), which is always interpreted the same.

    Also, if you are passing data from the client, use parameterised statements; never inline values.