Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    109

    Unanswered: Default Date Format used by Mssql

    Hi there,

    What is the default date format used by transact sql? Would YYYY-MM-DD HOUR:MINECS format work on Mssql?

    I am working on a project that needs to work with atleast two databases (Mysql/Mssql). I use the above date format and while it works perfectly on all Mysql databases, it gives me trouble in some Mssql setups.

    Most of the trouble arises when I am doing INSERT or SELECT queries.

    How do I handle this? Is there some way that I can tell Mssql that I am using the yyyy-mm-dd format or should I find out what format that particular mssql is using and adopt it?

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    YYYY-MM-DD hh:mm:ss should work fine with mssql. that's basically the ODBC format.

    See http://msdn2.microsoft.com/en-us/library/ms187928.aspx for a list of all the different date formats in sql server.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've never had the ISO standard temporal format (YYYY-MM-DD HH:MMS.TTT) give me trouble with Microsoft SQL, that is actually the preferred format for dates and times. The only thing I've had trouble with Microsoft-SQL handling DATETIME values in that time format was because MS-SQL can only resolve time down to 3 ms so it sees all three of the following times as identical:

    2006-08-07 06:05:04.000
    2006-08-07 06:05:04.001
    2006-08-07 06:05:04.002

    If you are using SMALLDATETIME values, things get more interesting quickly, since those are only accurate to the minute. That might be a whole different issue.

    -PatP

  4. #4
    Join Date
    Aug 2003
    Location
    India
    Posts
    109
    One of my clients use Mssql and her server uses the YYYY-DD-MM date format. I had to change the date formatting in the code to make it work for her.

    I would really need to know whether this is an issue in some mssql servers.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, I don't know of ANY condition under which MS-SQL 7.0 or later versions have any problem interpreting dates formatted as YYYY-MM-DD as long as the date is valid for the datatype you are using.

    As I don't know what made you think you needed to change the date formatting, all I can do is say that the date format isn't the problem, something else is.

    -PatP

  6. #6
    Join Date
    Aug 2003
    Location
    India
    Posts
    109
    Yeah, When I changed my date format, the queries worked. So, the problem is really with the format.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe I'm not making this clear, but the ISO string format you are using for the date is NOT the problem. I don't know what is the problem, but the ISO format isn't it.

    -PatP

Posting Permissions

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