Microsoft KB Archive/220918

= HOWTO: Using SQL DMO to Print Date in Regional Format =

Article ID: 220918

Article Last Modified on 11/3/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q220918



SUMMARY
This article demonstrates how to use two different methods using SQL Distributed Management Objects (DMO) to return dates formatted to a specific regional locale.

By default, the date format for SQL server is in U.S. date format MM/DD/YY, unless a localized version of SQL Server has been installed. The SET DATEFORMAT statement and sp_addlanguage will not change the display format for dates.



MORE INFORMATION
Here are two ways to resolve this issue:
 * Use the SQL Server native function Convert to display dates in the format desired. The SQL DMO ExecuteWithResults method can retrieve a date converted to a preferred format by using the Convert function inside a Select statement.

-or-
 * Set the SQLServer.RegionalSetting property to TRUE and make sure that the workstation locale in Control Panel's Regional Settings is set to the necessary region.

Using the Convert Function to Display in Format Preferred
SQL DMO ExecuteWithResults can retrieve a date converted to a desired format by using the Convert function inside a Select statement, as illustrated in the following example code: Dim oSQLServer As SQLDMO.SQLServer Set oSQLServer = New SQLDMO.SQLServer oSQLServer.LoginTimeout = 15 'oSQLServer.ODBCPrefix = False Dim oQueryResult As Object oSQLServer.Connect "", "" Set oQueryResult = oSQLServer.ExecuteWithResults("SELECT                      CONVERT(char(12), GETDATE, 13)") Debug.Print oQueryResult.GetColumnString(1, 1) The last parameter in the Convert function is to apply Style 13, which is the European default (dd mon yyyy hh:mm:ss:mmm) of the date format.

Using the SQL DMO RegionalSetting Property
 Change the workstation locale. Click Start, point to Settings and then select Control Panel. In the Control Panel dialog box, double-click Regional Settings. In the Regional Settings dialog box, click the Regional settings and then select "English(United Kingdom)" from the drop-down listbox. Make sure the "Set as system default locale" option is selected. Install any required components and reboot the computer if prompted.  Set the SQLServer.RegionalSetting property to TRUE in your DMO code: Dim oSQLServer As SQLDMO.SQLServer Set oSQLServer = New SQLDMO.SQLServer oSQLServer.LoginTimeout = 15 oSQLServer.ODBCPrefix = False Dim oQueryResult As Object oSQLServer.Connect "", "" oSQLServer.RegionalSetting = True Debug.Print oSQLServer.Databases(1).Tables(1).CreateDate The CreateDate method will print in English(United Kingdom) date format. 

