I only ever want the date portion and not the time. Would that still be datetime2(3)?

Michelle Great question. If you truly only ever need the date and never the time, then yes, you can use the SQL Server date data type. That will store just the calendar date with no time portion at all.

However, the reason I'm recommending datetime2(3) for this course, especially when you're bringing data over from Microsoft Access, has to do with compatibility during imports and data transfers.

Even though Access often displays dates without a time, or shows time only down to the nearest second, internally Access stores date/time values with fractional seconds. Functions like Now() include milliseconds whether you see them or not. That extra precision lives behind the scenes in the table.

When you try to move that data into SQL Server, if your destination field is only a date (or even an older datetime type with less precision), SQL Server may reject the record or truncate the value because it can't store the full timestamp Access is sending.

By using datetime2(3), you're giving SQL Server enough precision to accept the incoming value from Access without errors, rounding issues, or import failures. It basically acts as a compatibility buffer between the two systems.

So the short version is: If you're working strictly inside SQL Server and only need dates, then date is perfectly fine. But if you're importing, linking, or syncing data from Access, sticking with datetime2(3) will save you headaches and keep everything playing nicely together.

Explained very well! Thanks Rick

When trying to update the DateTime field i get this error and do not know why I closed and reopened and still get this error.

Looks like I figured it out with the help of Gemini, not sure if this was a setting you had everyone turn off and I missed it or if you have not talked about this setting yet.

Brent Which setting forced you to have to recreate the table? What did you change? Which setting are you indicating with your screenshot there?

Richard Brent is talking about this setting:

Prevent saving changes that require table re-creation

When I tried to Change to Date2(3) as you were mentioning above it would only let me update after unchecking that box Prevent saving changes that require table re-creation.

Yeah, that's most likely because the table initially had data, which it considers a different type in that field. It won't let you save changes if the data that's already in there doesn't match the current data type. It's a bit of a pain, but access is more forgiving that way. You can take long text fields and convert them to short text, and it'll just chop off everything after 255 characters. SQL Server isn't as forgiving.

That's one of those cases where you're better off adding a new field and then using an update query to copy the data over, converting it properly if necessary, and then just delete the old field.