CHANGING DATE FORMAT OF SQL SERVER DATABASE

Question

CHANGING DATE FORMAT OF SQL SERVER DATABASE

MOON 60

I have a database in which smalldatetime field exists. Its format is YYYYDDMM, yet I prefer YYYYMMDD. It could be set using set dateformat command, but it is only valid for current connection. I am looking for a solution to change date format permanently unless the database is recreated.

Erland Sommarskog 127.4K Reputation points MVP Volunteer Moderator

2025-09-13T20:28:01.6233333+00:00

Date and time data types in SQL Server have no format. They are stored as binary values. They can be converted to and from readable strings either in SQL Server or in the client.

Thus, when you say the field has a format, this may reflect how you see the value in your application. But then you talk about SET DATEFORMAT, however, that only affects how SQL Server interprets strings when converting to data.

Can you clarify with an example of what you are seeing and experiencing?
MOON 60 Reputation points

2025-09-14T15:20:57.7766667+00:00

select mydate from mytable where mydate > '2025-21-10' order by mydate desc statement results in:

If the date was 2025-10-21, the statement would return an error.
Lakshmi Narayana Garikapati 470 Reputation points Microsoft External Staff Moderator

2025-10-23T09:57:25.8866667+00:00

Hi @MOON,

I’m following up to check if the response provided below was helpful.

Thank you for reaching out to the SQL Forum. Please let us know if your issue has been resolved. If you still have any questions or need further assistance, don’t hesitate to get in touch we’re happy to continue supporting you.

We appreciate your participation and thank you for being an active member of the community.

Best regards,

Lakshmi

1 answer

Your answer

Erland Sommarskog 127.4K Reputation points MVP Volunteer Moderator

2025-09-13T20:28:01.6233333+00:00

Date and time data types in SQL Server have no format. They are stored as binary values. They can be converted to and from readable strings either in SQL Server or in the client.

Thus, when you say the field has a format, this may reflect how you see the value in your application. But then you talk about SET DATEFORMAT, however, that only affects how SQL Server interprets strings when converting to data.

Can you clarify with an example of what you are seeing and experiencing?
MOON 60 Reputation points

2025-09-14T15:20:57.7766667+00:00

select mydate from mytable where mydate > '2025-21-10' order by mydate desc statement results in:

If the date was 2025-10-21, the statement would return an error.
Lakshmi Narayana Garikapati 470 Reputation points Microsoft External Staff Moderator

2025-10-23T09:57:25.8866667+00:00

Hi @MOON,

I’m following up to check if the response provided below was helpful.

Thank you for reaching out to the SQL Forum. Please let us know if your issue has been resolved. If you still have any questions or need further assistance, don’t hesitate to get in touch we’re happy to continue supporting you.

We appreciate your participation and thank you for being an active member of the community.

Best regards,

Lakshmi

Answer 1

The best way to deal with this problem is to use the format YYYYMMDD without delimiters, for instance 20251021. This format is always interpreted in the same way with all date-related data types in SQL Server.

The format YYYY-MM-DD is also always understood correctly with the newer types datetime2 and datetimeoffset, but not the older data types datetime and smalldatetime, but here the interpretation depends on the DATEFORMAT setting, which in its turn takes its default from the language setting. Which in its turn defaults to us_english when you install an English version of SQL Server. With us_english follows the format mdy, and some smart person in California(*) realised that if the string is NNNN-NN-NN, then this can't be month-day-year, but apparently the year must come first. But apparently, the very idea that no one would ever write a date with year first was so alien, that they did not bother to do a reality check to see if any of the formats were used in practice. (In which case they would have found that YYYY-MM-DD is ISO standard, and YYYY-DD-MM is extremely little used, if at all.)

You can change your personal default language with this command:

ALTER LOGIN youruser WITH DEFAULT_LANGUAGE = British

From now on, your session will default to the DATEFORMAT setting dmy.

It is also possible to set a server-wide default for all new logins that are created on the server.

However, none of these options is nothing I recommend. The best is to stick to the unambiguous formats and leave the defaults as they are.

(*) California is where Sybase, who originally designed SQL Server, are located.

MOON 60 Reputation points

2025-09-15T16:32:57.4866667+00:00

Delimiter free approach is fine; however, 202599 expression is invalid, while 2025-9-9 expression is valid.
Erland Sommarskog 127.4K Reputation points MVP Volunteer Moderator

2025-09-15T21:02:13.7833333+00:00

Yeah, with delimiter-free format, zeroes are mandatory. And for that matter, I always use them when I have delimiters as well. I would never write 2025-9-9, that looks just weird to me.

Share via

CHANGING DATE FORMAT OF SQL SERVER DATABASE

1 answer

Your answer