Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2001
    Posts
    9

    Red face Unanswered: Date format independant of local settings

    Hello,
    how can we make the Date format for SQLServer7.0 independant of locale settings. We have a Web Server and a database server running multiple projects having different requirement for date formats viz mm/dd/yyyy and dd/mm/yyyy.

    I suppose SQLServer takes the database local date format as the current date format. The problem can be solved if i can indicate in my SQL queries the format being used in the query. This also eliminates any accidental change in database server format (from mm/dd/yyyy to dd/mm/yyyy), which would mean 1st Feb being inserted into the database as 2nd Jan.


    Any Ideas !!!

    Regards,

    Ashutosh

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Have you looked at SET DATEFORMAT

    Code:
    SET DATEFORMAT mdy
    GO
    DECLARE @datevar datetime
    SET @datevar = '12/31/98'
    SELECT @datevar
    GO
    
    SET DATEFORMAT ydm
    GO
    DECLARE @datevar datetime
    SET @datevar = '98/31/12'
    SELECT @datevar
    GO
    
    SET DATEFORMAT ymd
    GO
    DECLARE @datevar datetime
    SET @datevar = '98/12/31'
    SELECT @datevar
    GO

  3. #3
    Join Date
    Dec 2001
    Posts
    9

    prob. solved using SET DATEFORMAT

    Hello,
    thanks the problem seems to be solved by using the SET DATEFORMAT command. While ADODB connection object we have to execute the above command as a action query -
    Connection.Execute "SET DATEFORMAT dmy"

    The connection thus follows this new date format.

    Thanks Again :-)

    Regards,

    Ashutosh

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    This is only a problem with character date formats.
    If you always transfer dates with format yyyymmdd then you should never have a problem.

    dd mmm yyyy also works as long as you don't use other languages.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •