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

Flaviana Ilao 0

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.

PercyTang-MSFT 12,426 Reputation points Microsoft Vendor

2023-04-04T05:24:23.07+00:00

Hi @Flaviana Ilao Can you show some of your data samples? This allows us to conduct tests.
Zularnain Jabbar 0 Reputation points

2023-04-04T05:43:14.2233333+00:00

Yes, that is correct. In some countries, including India, the date format changes from YYYYMMDD to YYYYDDMM if the month and date are both below 13. This is known as the "little-endian" format. For example, if the date is January 2nd, 2023, it would be represented as follows: nullnull

This can be confusing for people from countries that use the "big-endian" format (YYYYMMDD), so it's important to be aware of the date format used in the country you are working with.

Jackootwala CEO at Jackpot Results
Flaviana Ilao 0 Reputation points

2023-04-04T06:12:15.24+00:00

in SQL2008, record is inserted in correct form yyyy-mm-dd. for for some reason, in sql2019, some records are inserted in wrong format. there is no issue in 2019 in the date (DD) or month (MM) is more than 12. Below are sample data: Records in blue font is as expected. Working fine in SQL2008. WHile records in red font were inserted in SQL2019. In SQL2019, not all records are in the wrong format. Noticed if the MM or DD is higher that 12, record is inserted in YYYY-MM-DD correctly.
Flaviana Ilao 0 Reputation points

2023-04-04T06:16:36.5266667+00:00

Records in red font were inserted in SQL2019 while blue ones were inserted in SQL2008. The records are inserted into the database by dtsx file. If the month or date is higher that 12, record is inserted correctly in SQL2019.
PercyTang-MSFT 12,426 Reputation points Microsoft Vendor

2023-04-14T08:47:11.6133333+00:00

Hi @Flaviana Ilao We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Flaviana Ilao 0 Reputation points

2023-05-09T11:16:33.3+00:00

Thank you all for your inputs.

hi @PercyTang-MSFT , we did not modify the DTXS file that is responsible for inserting the data into the database. Currently, we are trying to workaround the data (with incorrect date format) after the wrong data was inserted into the database.

2 answers

Olaf Helper 43,246 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.
Please sign in to rate this answer.
Flaviana Ilao 0 Reputation points

2023-04-05T07:52:03.68+00:00

Regional setting is United States. SHort date format is M/d/yyyy
Sign in to comment
Erland Sommarskog 107.2K 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.
Please sign in to rate this answer.
Flaviana Ilao 0 Reputation points

2023-04-05T07:56:05.4833333+00:00

the data is inserted using SSIS package. this is the expression: SUBSTRING(Columnname,5,2) + "/" + SUBSTRING(Columnname,7,2) + "/" + SUBSTRING(Columnname,1,4). Datatype is Unicode string [DT_WSTR] 10

Flaviana Ilao 0 Reputation points

2023-04-05T07:57:55.5066667+00:00

ran the script you sent and i got the results below: i d 1 2022-07-02 00:00:00.000 2 2022-02-07 00:00:00.000 3 2022-02-07 00:00:00.000

Erland Sommarskog 107.2K Reputation points

2023-04-05T21:30:46.9066667+00:00

ran the script you sent and i got the results below: i d 1 2022-07-02 00:00:00.000 2 2022-02-07 00:00:00.000 3 2022-02-07 00:00:00.000

Yes, that is the expected result. The point is to show that the date interpretation depends on the DATEFORMAT setting. That setting implicitly depends on the user language. Here is a second demo:

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

You can check your settings with the command DBCC USEROPTIONS. Note that your settings in SQL Server has no relations to your regional settings in Windows.

the data is inserted using SSIS package. this is the expression: SUBSTRING(Columnname,5,2) + "/" + SUBSTRING(Columnname,7,2) + "/" + SUBSTRING(Columnname,1,4).

If you remove the slashes, you should be safe. However, you should still pass the values as parameters. (I don't know SSIS, so I cannot give the details.)
Sign in to comment

Share via

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

2 answers