Hi I have seen collation affects var char, I need to change the date and number and currency also in the same language but found that after using the set language it affects the text of the date not the number, is there any way that I can change the number, currency or number part of date? like can we see numer of the date part in arabic or roman

Hi @rajesh yadav , SQLServer sorting rules only affect columns of character type, for example: char, varchar, text, nchar, nvarchar, ntext, so in the query view sys.columns the sorting rule for non-character fields shows NULL. Although the database sorting rules can be changed, but it is problematic, because even if the database sorting rules are changed, the sorting rules of the fields in the library table may still be the original, not changed, which in use, there may be problems, so the database sorting rules try not to change at will. Best regards Niko If the answer is the right solution, please click " Accept Answer " and kindly upvote it. If you have extra questions about this answer, please click " Comment ". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

I have seen collation affects var char, And it really only effects texts stored in a column of type varchar; and absolutly no other data types. date, integer etc. are only values without any format information; the client formats those values, not the database engine.

Numbers and dates are stored in binary format, they are not stored in text format. They are displayed as strings in the UI, for instance SSMS, because humans can read strings better than binary values. But don't confuse storage format and presentation format. Interesting enough, SSMS does not seem to have any option to respect Regional Settings. But if you write your own application, you can use whichever string representation you like.

how to change the number of date in the same way collation does for varchar.

Accepted answer

NikoXu-msft 1,911 Reputation points

2022-08-29T05:51:40.697+00:00

Hi @rajesh yadav ,

SQLServer sorting rules only affect columns of character type, for example: char, varchar, text, nchar, nvarchar, ntext, so in the query view sys.columns the sorting rule for non-character fields shows NULL.
Although the database sorting rules can be changed, but it is problematic, because even if the database sorting rules are changed, the sorting rules of the fields in the library table may still be the original, not changed, which in use, there may be problems, so the database sorting rules try not to change at will.

Best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
rajesh yadav 171 Reputation points

2022-08-30T04:40:32.717+00:00

thank u,

One more clarification , i have seen collation does not affect nvarcahr, but according to me the style part of collation that is" _CP1_CI_AS" affects nvarchar( i am not sure pls conferm then how sorting is done for nvarchar) , and i think it is the style part of collation ex . this is the collation " SQL_Latin1_General_CP1_CI_AS" it has designator part "SQL_Latin1_General" and style part "_CP1_CI_AS"

so pls conferm this, that the, style part is the only configuration which affects or it has some other story?

NikoXu-msft 1,911 Reputation points

2022-08-30T06:05:17.263+00:00

Hi @rajesh yadav ,

_CI(CS) is case-sensitive or not, CI is not, CS is (case-insensitive/ case-sensitive)
_AI(AS) Whether to distinguish between accents, AI does not, AS does (accent-insensitive/accent-sensitive)
_KI(KS) Whether to distinguish between pseudonym types, KI does not, KS does (kanatype-insensitive/kanatype-sensitive)
_WI(WS) Whether to differentiate between full and half angles, WI does not differentiate, WS does (width-insensitive/width-sensitive)
For details, please see：
https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15#Collation_Defn

Best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Erland Sommarskog 109.8K Reputation points MVP

2022-08-30T21:02:59.833+00:00

The collation affects the sorting, yes. And that includes the CI/CS part. Look at this:

INSERT #test(a, b) VALUES ('Alpha', 1), ('average', 3), ('Amsterdam', 7), ('alpha', 8) SELECT a, b FROM #test ORDER BY a COLLATE SQL_Latin1_General_CP1_CI_AS SELECT a, b FROM #test ORDER BY a COLLATE SQL_Latin1_General_CP1_CS_AS

In a CI (case-insensitive collation), case does not matter, and how alpha and Alpha sorts depends on the second column.

In a CS (case-sensitive) collation, lowercase sorts before uppercase for nvarchar - but only if there is no other difference. Therefore alpha sorts before Alpha, but Amsterdam still sorts before average. That is, case is a second-level sorting criteria.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

how to change the number of date in the same way collation does for varchar.

2 additional answers

Your answer