Web Development

Ask something
Discussion Locked

SQL 7 date format and foreign languages

0 helpful
6 replies

by esrfalco 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
SQL 7 in US English requires that the Windows date format default be set for US English date format. Does anyone know if foreign language versions of SQL have this same requirement? That is, if I install the Spanish version of SQL 7, what Windows date format will it require?

6 replies

by donq

When used in this country set the Date Format to "mm/dd/yy" (or English Standard). When used in Europe (or outside the US) set the data format to "dd/mm/yy" (or European Standard). Whenever and wherever Windows is used its data format can be customized to meet user requirements???

by esrfalco

I know how to set the Windows date default. That's not the issue. The issue is the corruption of the SQL database, in which each transaction is date stamped. The US English version of SQL expects mm/dd/yy, so if the Windows date format is other thanthat you will eventually corrupt the SQL database.

Question: What date format to foreign language versions of SQL expect?

by wjlarue

Lets address three things about SQL Server:
1. How dates and times are stored?
2. What the default language setting does?
3. What is the difference between US and Spanish versions?

First, you don?t have to worry data corruption. SQL Server stores all dates the same way regardless of any other international settings or version. This is where SQL Server basically stores a very large number, not easy for humans to read or interpret, but really easy for computers to do datetime calculations.
SQL Server stores values with the datetime data type internally as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system?s reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

(Continued in following comment)

by wjlarue

(This continues the answer)

Second, the default language option that is first set on install and changeable later on the server tab in the SQL properties is merely used for how SQL Server displays the date (by default, this can be modified using CONVERT) and how it displays information for some of its datetime functions. SQL Server supports twenty-four languages and each selection has a direct affect on these listed items:
1. The format in which dates are presented:
a. dmy (Day, month, year)
b. mdy (Month, day, year)
c. ymd (Year, month, day)
2. Short and long names for each month.
3. Names for each day.
4. Which day is considered the first day of the week.

Now for the last point, Microsoft has created SQL Server in French, German, Spanish, and Japanese. The call these ?localized? copies because they display text through the system based on the local language (menus, errors, system messages, etc.). Everything mentioned above works exactly the same on all international version of SQL Server.

Hope this explains it. If you have further questions about this, just post a comment and I will try to answer it.

Jeff LaRue
Raging Technologies

This discussion is closed to new replies.