When writing and working with T-SQL in SQL Server, you'll often come across the number 1033. In many cases, that will have the word English associated with it. But what are these numbers, and which ones does SQL Server know about?
The number 1033 comes from the reference to a Windows Locale ID. You'll find the list of Windows Locale IDs here:
https://msdn.microsoft.com/en-us/library/ms912047(v=winembedded.10).aspx
It's a big list with entries that look like this:
Here are the entries for English:
Notice that 1033 is the default English locale and it's the one for the United States. (Much to the dismay of my English friends in the UK who actually thought they owned the language, that their dialect would be the default, and the US version would just be a variant).
Anyway, how do we relate these to SQL Server, and which ones does it know?
We'll find these in the sys.syslanguages system view (wrapped for clarity):
Notice that it shows the names of the months and days, and has a mapping from the lcid locale ID values across to the language IDs that are used in the sys.sysmessages (which contains the error messages that come from SQL Server):
I am struggling with date formats today. In SQL I am well able to handle pretty much everything that dates can throw at me. My problem today is with dates in SSIS packages. I have a text file with dates in a useful YYYY-MM-DD HH:MM:SS format. I can suck the data in as text, and put in a table – nothing too surprising there. But then if try to use SSIS to work with data, the problems start. SQL is set up as British English, whereas SSIS appears to be US English. I can suck the data in where DD <=12, but it then swaps the month and day. I am using the DT_DBTIMESTAMP datatype, which I thought used the SQL 121 date format, which is how my data is arriving. But I am not winning!
Hi Pete,
With SQL Server (anywhere), YYYY-MM-DD HH:MM:SS is a format that I don't use, as it's language-dependent with some data types, and not with others. You either have to use the whole ISO8601T format (including the T) or remove the dashes from within the date part.
HTH,
Greg