HOWTO: Using SQL DMO to Print Date in Regional Format

ID: Q220918

The information in this article applies to:

Microsoft SQL Server version 7.0

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 "<my70Server>", "<Uid>"
   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 "<mySQL7Server>", "<Uid>"
   oSQLServer.RegionalSetting = True
   Debug.Print oSQLServer.Databases(1).Tables(1).CreateDate

The CreateDate method will print in English(United Kingdom) date format.

REFERENCES

SQL Server 7.0 Books Online, topic: "SQL-DMO"

Additional query words: kbDSupport

Keywords : kbDatabase kbOLEDB kbSQLServ kbVC kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbhowto