Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012

    Unanswered: Date format issue

    Hi, Im trying to populate a table with dates in one of the columns, I have had numerous issues to do with the original table (wrong datatype for date) so I decided to recreate the table with the correct datatype, however this has brought about another challenge, the format! it is using the american format of mmddyy instead of the english format of ddmmyy, how can I resolve this, I cant populate alot of my records into the table and the ones that I have are corrupted due to the format! is there a simple way to fix this!


  2. #2
    Join Date
    Nov 2004
    Provided Answers: 4
    The DATE data type is internally represented in some binary format. Only when it must be displayed, it gets converted to some string format, like mm-dd-yyyy, dd/mm/yyyy or whatever.

    In the opposite way, you must tell MSSQL in what data format you are passing the strings that represent dates, so it can use the correct logic to convert the character strings to the internal DATE representation. When you don't, MSSQL will guess in what format the date is represented, resulting in dates that are sometimes correct, like 01/01/2012, but 01/02/2012 will be converted to February the first.

    INSERT INTO MyTable (aDateColumn) 
    VALUES(CONVERT(DATE, '31/01/2012', 103)) -- for DD/MM/YYYY format
    This is a good place to find out about DATE and DATETIME formats.

    You will have to show us the CREATE TABLE script (DDL) of your table and some sample data if you need more help.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming 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