Search code examples
sqlsql-server-2008-express

SQL Query doing strange thing

If I run this query on SQL Server Express 2008 : 

Insert NoteBook (Date, Note) Values ('11/04/2011 11:02:46', 'test')

It stored the date as 04/11/2011

How can I prevent this?

Solution

  • Use the ISO-8601 format: YYYYMMDD (or YYYY-MM-DDTHH:MM:SS) - it works always, regardless of your SQL Server language and locale settings. 

    INSERT INTO dbo.NoteBook(Date, Note) 
VALUES('2011-04-11T11:02:46', 'test')

    The date in SQL Server is NOT stored in any particular string-oriented format - a date is a date is a date, regardless of what you see.

    You see a string representation of the date - but again: it's NOT stored that way - and thus you cannot "prevent" it from being stored that way...

    Check your language settings in SQL Server:

    SELECT @@LANGUAGE

    What language do you have?? The language defines the default format in which dates are shown.