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!
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