Date Format

• joelucas

  Ten Centuries

  Points: 1078

  More actions

  October 30, 2003 at 1:54 am

  #65433

  Is there a way I can set the date format at a database level rather than the SQL Server 2000 instance level?

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  October 30, 2003 at 5:11 am

  #480267

  No, date formats (and the issue is usually with dmy vs. mdy) are determined by the default language, which is for the instance, not database. You can, of course, alter the settings per session (SET DATEFORMAT, SET LANGUAGE), and these settings can assure consistency within stored procedures. Another solution that may work for you is to set the language in the front-end's connection string, e.g.:

  
  
    Dim strConnUS As String
  
    Dim strConnBE As String
  
    Dim aConnUS As New ADODB.Connection
  
    Dim aConnBE As New ADODB.Connection
  
    Dim aRS As ADODB.Recordset
  
   
  
    strConnUS = "Provider=SQLOLEDB;Server=SQLSVR9;Database=Northwind;Trusted_Connection=Yes"
  
    strConnBE = "Provider=SQLOLEDB;Server=SQLSVR9;Database=Northwind;Trusted_Connection=Yes;Language=British English"
  
    With aConnUS
  
      .ConnectionString = strConnUS
  
      .ConnectionTimeout = 1
  
      .Open
  
    End With
  
    With aConnBE
  
      .ConnectionString = strConnBE
  
      .ConnectionTimeout = 1
  
      .Open
  
    End With 
  
    Set aRS = aConnUS.Execute("SELECT * FROM Orders WHERE OrderDate > '12/31/1997'")
  
    Set aRS = aConnBE.Execute("SELECT * FROM Orders WHERE OrderDate > '31/12/1997'")
  
  

  Of course, if the application instead uses a parameterized command object, this sort of input issue is moot.

  --Jonathan

  
  
  --Jonathan