4 Answers
4

DateTime values should be inserted as if they are strings surrounded by single quotes:

'20100301'

SQL Server allows for many accepted date formats and it should be the case that most development libraries provide a series of classes or functions to insert datetime values properly. However, if you are doing it manually, it is important to distinguish the date format using DateFormat and to use generalized format:

Set DateFormat MDY --indicates the general format is Month Day Year
Insert Table( DateTImeCol )
Values( '2011-03-12' )

By setting the dateformat, SQL Server now assumes that my format is YYYY-MM-DD instead of YYYY-DD-MM.

Is that correct? You Set DateFormat MDY, but later you say that SQL Server now assumes my format is YYY-MM-DD. Why doesn't SQL Server expect your format to match the Set DateFormat and look for MM-DD-YYYY? I'm not saying it's wrong (I don't know), but it seems counter-intuative.
–
JerryOLMar 13 '11 at 21:21

2

@JerryOL - The Set DateFormat MDY only determines how SQL interprets the the order of month and day, not the exact format. You can try it for yourself. If you call Set DateFormat DMY, 2011-03-12 is Dec 3rd. If you call Set DateFormat MDY, that same date is March 12th.
–
ThomasMar 13 '11 at 22:41

1

@JerryOL - Btw, I could have also used YMD or YDM and achieved the same effect however I find that those formats are only useful if you are not passing the four digit year.
–
ThomasMar 13 '11 at 22:43