0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TeaDrinkingGeek
  • 1,995
  • 5
  • 34
  • 52
  • @marc_s this should be an answer, not just a comment. – Adam Matan Apr 11 '11 at 10:30
  • This has been done to death already, not least here http://stackoverflow.com/questions/1138142/best-way-to-convert-and-validate-a-date-string – gbn Apr 11 '11 at 10:36

1 Answers1

4

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • US English. Where can I change it, or should I change it if Im going to use yyyymmdd? – TeaDrinkingGeek Apr 11 '11 at 10:31
  • @teadrinkinggeek: if you use `yyyymmdd` you don't need to change anything - that ISO-8601 format **always** works. Otherwise, you need to look at [SET DATEFORMAT](http://msdn.microsoft.com/de-de/library/ms189491.aspx) in SQL Server - this allows you to define whether your strings will be interpreted as `MM/DD/YYYY` (for the US) or `DD/MM/YYYY` ([for pretty much the rest of the world](http://en.wikipedia.org/wiki/Date_format_by_country)) – marc_s Apr 11 '11 at 10:33
  • 1
    You're right! I used INSERT INTO dbo.NoteBook(Date, Note) VALUES('2011-04-11T11:02:46', 'test') and it showed it correctly. Thanks. – TeaDrinkingGeek Apr 11 '11 at 10:37