Change DateTime
By Richard Rost

15 days ago

Lesson 7: Switching Dates to to DateTime2(3)

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this video, we clarify a previous recommendation about using DateTime2(0) for SQL Server fields when working with Microsoft Access data. We discuss why switching to DateTime2(3) is a better choice, as Access actually stores milliseconds even if they are not displayed. I will show you how to update your existing fields in SQL Server using an ALTER TABLE statement to avoid import and update errors when migrating data from Access, and we briefly discuss the technical reasons for these issues and the difference between the graphical designer and direct SQL commands.

Navigation

Prev: Lesson 06
Next: Lesson 08

Keywords

SQL Server for Access, DateTime2(3), DateTime2(0), fractional seconds, milliseconds, Now function, import data, SQL Server, migration, update errors, truncation errors, ALTER TABLE, SSMS, graphical designer, column precision, field size, change column typ

Comments for Change DateTime

Age	Subject	From
33 hours	Thank You!	Monica Jones
13 days	ABCD and SQL	Juan Rivera
14 days	Changing Data Type in SSMS	Sami Shamma
14 days	PostgreSQL	Philip Hurzeler

Subscribe to Change DateTime
Get notifications when this page is updated

Intro

Transcript

Okay, before we move on to some new material, I want to make a quick adjustment to something I said earlier about date and time fields. Previously, I told you to use DateTime2(0) in SQL Server, and honestly, that's still perfectly fine for most databases.

Access doesn't display fractional seconds on the screen. Remember that number in parentheses is the number of decimal points after the second you get: tenths of a second, milliseconds, those kinds of things. For 99 percent of the stuff we do, if not more, whole seconds are more than good enough.

However, while I was working off screen with some stuff I'm planning to cover in future lessons, especially importing data on a larger scale from Access into SQL Server, which I know all of us are going to be doing, I ran into something that's worth fixing now while we're still pretty early in the course.

Essentially, in Access, the Now function actually stores time down to the milliseconds. You don't actually see it because Access formats dates and times in a way that hides those fractions, but they're in there. It's like Ragu. It's in there. Or is that Prego? One of those spaghetti sauces. They're in there. They're stored in the table.

So if you have a field defined in SQL Server as DateTime2(0), it literally cannot store fractional seconds. That means if you try to push a Now value up to SQL Server, sometimes you'll get errors because SQL Server is basically saying that value has more precision than this field will allow, and you'll see truncation errors or "field too small" type errors. So we're going to try to avoid that now.

Yes, I know we could fix that by converting or truncating the value every time we send it up to SQL Server. We could strip off the fractional seconds, but I don't want you to have to remember that everywhere. It's just one more little annoying gotcha that's going to pop up later. So instead, I'm changing my recommendation for SQL Server for Access users. We're going to use DateTime2(3), and that will store up to the millisecond, even though we don't always need them.

Now, DateTime2(7) is the max, and we really don't need that much precision. Access only works down to milliseconds anyway, and milliseconds is three digits. So DateTime2(3) lines up perfectly with what Access actually produces. It stores three digits of fractional seconds, will avoid those update errors, and it's going to make your life a lot easier when you're migrating data or doing imports from Access.

As far as storage, it's not that big of a deal. DateTime2(0) uses six bytes, DateTime2(3) uses seven bytes, and DateTime2(7) will use eight bytes. So we're basically talking about one extra byte per record. I'll take that trade all day over not having to worry about truncating seconds.

Generally, to make the change now, if you don't have any data in it or as long as all of your data is compatible, you can right-click, modify that column, or that field. Come down here and change this zero to a three. Everything in there right now is a zero, so it should work. Hit save.

Now, sometimes this will come up: it says "Saving change is not permitted. It will require the following tables to be dropped and recreated." Whenever you see that, hit Cancel. It's basically the SSMS GUI, the graphical interface, telling you that you can't do it. You can still do it; you just have to do it with some SQL.

So close this, don't save it. Go to a new query. Now we are going to get ahead of ourselves a little bit, but you can do anything you need to do without using the graphical designer. You can just write an SQL statement for it.

For those of you who've taken my more advanced SQL classes in Access, you can type in:

ALTER TABLE CustomerT
ALTER COLUMN CustomerSince DATETIME2(3);

Hit Execute, and there you go. Now it's changed to a DateTime2(3). You don't see it over here immediately. Click on the CustomerT and then hit Refresh (F5), and notice it will come back and now it's DateTime2(3).

Why does that happen when it works here but doesn't work in the graphical designer? Well, you could think of SSMS's table designer, the whole graphical interface, as like issuing a command from the bridge of the Enterprise. You tell the helmsman, "Change course to 270 mark five," and the helmsman looks at his panel, checks the safety protocols, checks the nav computer, checks whether the maneuver is within operating parameters, and sometimes comes back and says, "I can't do that, Captain." Not because the ship can't do it, but because the bridge systems are designed to prevent a first-year ensign from damaging the whole ship.

But now if you get on the comm and talk to Scotty, he goes, "Aye, Captain, I'll reroute the plasma flow and bypass the safeties. I'll make it happen." That's the difference between SSMS and talking directly to the SQL Server engine. SSMS is the bridge interface. It has guardrails, it tries to protect you, it blocks changes it thinks might be dangerous or complicated. But when you run it as an SQL statement - the ALTER TABLE statement - you're talking right to the warp core. You're giving the order directly to the engines. Scotty's going to make it happen, as long as it's technically possible.

That's going to do it for that. I had to insert this before we went too much further because I'm starting to see a lot of feedback in the forums and in the YouTube comments. People have mentioned stuff after watching the first six lessons, so I wanted to put this in here now before we did too much more with dates and times.

I'm going to insert one more lesson. I was going to get to queries next, but I want to do connecting to your SQL Server from another machine because that's the number one comment I'm seeing everybody bring up in the forums. People say, "Okay, I got it working, I can get on my SQL Server machine, how do I connect to it from my workstation?" That's a big deal. There's a lot to it, and we're going to cover that in the next video. Hang in there, we're going to get to all that stuff, and then queries are right after that.

Oh, and one quick nerd note before we wrap this up. Earlier I said that using DateTime2(0) could cause update errors when you send Now values from Access because of the extra fractional seconds. I want to clarify that just a little bit for the database purists out there, because I know some people are going to speak up in the comments and say, "But actually..." and act like they know everything.

Don't get me wrong, I love it when you catch things that I get wrong. Everybody goofs. I goof from time to time, too. I want to say that sometimes I teach things in the video just to keep it simple. I say that SQL Server has a problem with it. SQL Server itself can actually handle that just fine. If it receives more fractional precision than the column allows, it will round or truncate it without throwing an error.

Where the problem tends to happen is somewhere in the chain between Access and SQL Server. Remember, when you send data up, you're going through drivers, connection layers, parameter conversions, sometimes even string formatting, depending on how the update is happening. Somewhere in that pipeline, those hidden fractional seconds that Access stores internally can sometimes cause conversion or update failures.

I ran into this myself multiple times while migrating large sets of data, and that's what led me to revisit this recommendation. So widening the field to DateTime2(3) isn't because SQL Server can't handle it. It's just a practical interoperability tweak. It lines up better with what Access naturally produces and helps avoid those edge case update/import issues without having to make you think about it every time.

If you're building a pure SQL Server system with no Access front end, then DateTime2(0) is still perfectly fine in most cases. But for Access integration, use DateTime2(3). It's a much smoother path.

In the future, before you jump in the comments section, just keep in mind that not everyone is a level 10 database wizard nerd like some of us. I say things to keep it simple for most people. Even though I know what's going on behind the scenes, if you do catch me saying something incorrect, I do want to know about it - just be nice. Be nice in the comments.

I'll see you in the next lesson.

In lesson seven, we're going to make a quick adjustment to our date/time fields in SQL Server. Earlier, I recommended using DateTime2(0), which works fine in most cases. But after doing some larger data imports from Access, I discovered that Access actually stores fractional seconds internally, even though you don't see them on the screen.

So in this video, I'll show you why we're switching to DateTime2(3) and how to update your existing field so everything migrates cleanly moving forward. Now we've updated our date/time to standard DateTime2(3), so it lines up better with what Access actually stores behind the scenes and helps prevent import or update headaches later on. Trust me, you'll thank me later.

Post a comment down below. Let me know how you liked today's video and what you'd like to see in future lessons.

That's going to do it for lesson seven. Hope you learned something. Live long and prosper, my friends. I'll see you in the next video.

Quiz

Q1. Why was the recommendation changed from DateTime2(0) to DateTime2(3) when working with Access and SQL Server?
A. Because DateTime2(3) stores fractional seconds, matching what Access records
B. Because DateTime2(0) is incompatible with SQL Server
C. Because DateTime2(3) uses less storage space
D. Because Access cannot store fractional seconds

Q2. What happens if you try to import Access Now values into a SQL Server field defined as DateTime2(0)?
A. The import fails because of mismatched fractional seconds
B. The values are always rounded to the nearest day
C. SQL Server automatically converts them to text
D. No issue occurs; the import always works

Q3. How many digits of fractional seconds does DateTime2(3) store?
A. 1
B. 2
C. 3
D. 7

Q4. If you are using the SSMS graphical designer and cannot save a change to a column's data type, what should you do instead?
A. Restart SQL Server
B. Use an ALTER TABLE SQL statement
C. Reinstall the database
D. Change the field in Access first

Q5. What is the key storage difference between DateTime2(0), DateTime2(3), and DateTime2(7)?
A. The number of columns stored in the table changes
B. DateTime2(0) uses 6 bytes, DateTime2(3) uses 7 bytes, DateTime2(7) uses 8 bytes
C. Only DateTime2(7) can store null values
D. Storage size does not change among them

Q6. Why is it better to use DateTime2(3) when migrating data from Access to SQL Server?
A. It matches the precision of fractional seconds Access actually produces
B. It prevents all SQL Server errors forever
C. It automatically filters out bad data
D. It reduces overall database size

Q7. What is the effect of trying to store a value with more fractional seconds than allowed in a SQL Server column?
A. SQL Server will round or truncate the extra fractions
B. SQL Server will multiply the seconds by ten
C. SQL Server will reject all values with seconds
D. The value is stored as a string instead

Q8. In the context of the video, what analogy was used to explain the difference between using SSMS graphical designer and writing SQL directly?
A. Comparing a chef vs a cook
B. Cheeseburgers vs pizzas
C. The bridge and the warp core on the Starship Enterprise
D. Paintbrush vs pencil

Q9. Which of the following is a practical reason given to use DateTime2(3) rather than convert/truncate values before every import?
A. It simplifies the workflow and avoids repeated manual conversions
B. It improves database performance
C. It requires less SQL Server configuration
D. It guarantees zero data loss in all fields

Q10. If you are building a system with no Access integration, which data type is generally still fine to use?
A. DateTime2(7)
B. DateTime2(3)
C. DateTime2(0)
D. Text

Answers: 1-A; 2-A; 3-C; 4-B; 5-B; 6-A; 7-A; 8-C; 9-A; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on making an important update to our approach with date and time fields in SQL Server, particularly when working alongside Microsoft Access. Previously, I advised everyone to use the DateTime2(0) type in SQL Server. For the vast majority of cases, that suggestion was just fine, especially since Access does not even display fractional seconds by default.

However, after spending some time preparing materials for future lessons and performing significant data imports from Access into SQL Server, I ran into an issue you are likely to encounter as well. It turns out that the Now function in Access actually stores time values down to the millisecond. This level of precision is hidden by Access's default formatting, but the fractional seconds are still there, quietly stored in your tables.

The problem arises when you try to send these values from Access to a SQL Server field defined as DateTime2(0). That field type does not accept any fractional seconds. As a result, if you attempt to import or update records, you may encounter conversion or truncation errors, since SQL Server will reject the additional precision. While it is technically possible to strip off the fractional seconds from your data before importing, this extra step is tedious and error prone, and I do not want you constantly worrying about it.

A better solution is to switch your SQL Server fields to DateTime2(3). This stores date and time values down to the millisecond, which aligns perfectly with what Access naturally provides. Access only keeps time to three decimal places after the second, so this change matches both systems and prevents the import errors you might otherwise face. For those curious about storage, DateTime2(3) uses only one byte more per record than DateTime2(0), which is a small price to pay for smoother interoperability.

To make this adjustment, you just need to edit your DateTime2(0) field and change the zero to a three. You might see a message in SQL Server Management Studio (SSMS) stating that saving your changes is not permitted because it would require the table to be dropped and recreated. When this happens, simply cancel out of the dialog. You can still make the change by running a SQL statement directly. For example, you write an ALTER TABLE command to change your column type. Once you execute it, refresh your table, and you will see the new setting applied.

Think of SSMS's graphical tools as the control bridge with safety checks in place. It prevents you from making certain changes easily to keep you from breaking things accidentally. By issuing a SQL statement directly, you are effectively bypassing those safeguards and communicating straight with the engine, much like giving an order directly to the person running the engine room instead of using the ship's bridge.

I wanted to address this adjustment now, since I have started seeing more people encounter the same issue and mention it in forums and comments. Getting ahead of it early will save everyone frustration as we cover more advanced topics involving date and time fields.

Before we move on to working with queries in upcoming lessons, I plan to include a lesson on connecting to your SQL Server from another machine, since that is a common problem readers have asked about. After that, we will continue with queries and related topics.

One final technical note: while SQL Server itself handles excessive fractional seconds by rounding or truncating values when inserting into a DateTime2(0) field, the real trouble usually shows up in the data transfer path between Access and SQL Server. Various drivers and connection layers might misinterpret or mishandle the extra precision, leading to errors. The safest path when dealing with Access is to simply match the precision it actually creates, which is milliseconds or DateTime2(3). If you are only working within SQL Server and not using Access, DateTime2(0) remains a solid choice.

In summary, for anyone working with Access and SQL Server together, revise your recommendations from DateTime2(0) to DateTime2(3) for best results. If you notice something technical in my explanation that could be clarified, please let me know in the comments and remember to keep it friendly. It helps everyone improve.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Switching SQL Server date field from DateTime2(0) to DateTime2(3)
Explanation of Access and SQL Server date and time precision
Issues caused by storing Now values from Access in SQL Server
Fractional seconds handling in Access and SQL Server
Updating an existing SQL Server field using the GUI
Resolving SSMS "saving change not permitted" error
Altering a column with SQL using ALTER TABLE statement
Understanding data storage size differences in DateTime2 types
Recommendation for DateTime2(3) when integrating with Access
Practical impact of fractional seconds on data import and migration
Rationale for using SQL over SSMS designer for certain changes

Article

When working with date and time fields in SQL Server, especially if you are importing data from Microsoft Access, it is important to be aware of how each system handles precision. In previous guidance, you may have been advised to use the DateTime2(0) type in SQL Server for storing date and time values. This data type stores date and time down to the whole second, without any fractional seconds. In many cases, this is perfectly sufficient. However, there is a subtle but important detail to keep in mind: Access actually stores time values with precision down to milliseconds, even though it does not display the fractional seconds to you on the screen.

The number in parentheses after DateTime2 represents the number of decimal places for seconds. So DateTime2(3) gives you millisecond precision (three digits), which matches exactly the level of detail Access records internally. When transferring or synchronizing data from Access to SQL Server, if your SQL Server column only allows DateTime2(0), you may run into issues. For instance, when expressions like Now in Access are used, they include those hidden milliseconds. If you try to put that value into a DateTime2(0) field in SQL Server, you might receive truncation or conversion errors. This is because the value has more precision than the field allows, leading to messages about the field being too small or data being truncated.

While it is possible to write code to strip off the fractional seconds before every import, that is cumbersome and prone to mistakes. Instead, the best practice is to define your SQL Server date and time fields as DateTime2(3). This setting stores milliseconds and ensures you do not lose any of the data Access is actually storing. It also prevents those update and import errors without requiring you to remember any additional conversion steps.

The difference in storage is minimal: DateTime2(0) uses six bytes, DateTime2(3) uses seven bytes, and DateTime2(7) (the maximum precision) uses eight bytes. The extra single byte per record for milliseconds is a small trade-off for hassle-free data migration and consistency.

To make this change, edit your SQL Server table. If you try to do this from the table designer's graphical interface in SQL Server Management Studio (SSMS), you may encounter a message telling you that saving the change is not permitted because it would require the table to be dropped and recreated. If that happens, simply cancel out of the graphical designer and switch to running a direct SQL statement.

To alter a column's data type directly, you can use the following SQL command. For example, if you want to change the CustomerSince field in the CustomerT table to DateTime2(3), type this:

ALTER TABLE CustomerT
ALTER COLUMN CustomerSince DATETIME2(3);

After executing this command, the field will accept time values down to the millisecond, making it fully compatible with Access data. If you do not see the change immediately in SSMS, refresh the table list and it should update.

The reason this works when the graphical interface sometimes blocks it is that the designer in SSMS has built-in guardrails designed to prevent changes it deems dangerous or complicated. It may not permit changes that could require extra steps, even if the commands are otherwise valid. Running the SQL directly is like bypassing those safeties and speaking straight to the database engine, which will process your command as long as it is valid.

A note for those with advanced knowledge: SQL Server itself can handle values with more precision than the column allows, usually by rounding or truncating the extra precision and not throwing an error. However, when passing data from Access to SQL Server, the hand-off goes through drivers, connection layers, and conversion steps. Somewhere in that pipeline, those hidden milliseconds can cause update or conversion failures. That is why using DateTime2(3) is a practical fix for anyone doing imports or linked table work from Access.

If you do not use Access or are building a new application entirely within SQL Server, using DateTime2(0) is still fine for most needs. But if Access is part of your workflow, defining your SQL fields as DateTime2(3) will make life much easier and prevent frustrating problems down the line.

By making this small adjustment now, your future data migrations and updates will be smoother, and you will avoid the most common pitfalls that users encounter when combining Microsoft Access with SQL Server.