DateValue
By Richard Rost
4 years ago
Get Date Only from a DateTime with DateValue
In this Microsoft Access tutorial, I'll show you how to use the DateValue function to isolate the date part (only) of a DateTime value in Microsoft Access. This is handy if you have a lot of dates that include times but you only care about the date.
Pre-Requisites
Recommended Courses
Links
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, just the date, convert date/time to date only, get date from a datetime, datetime field date only
Subscribe to DateValue
Get notifications when this page is updated
Intro In this video, I will show you how to use the DateValue function in Microsoft Access to extract just the date portion from a date/time field. We will see why this is important when querying records for a specific date range, how to properly set up criteria to avoid missing records, and the benefits of using a calculated field to simplify your queries. I will also share a quick tip for using parameter queries with date ranges and highlight common mistakes to avoid when working with dates and times in Microsoft Access.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, I want to show you how to use the DateValue function to get just the date portion of a date/time field in Microsoft Access.
Why is this handy? Sometimes you have a date field like Order Date. Sometimes you use the Now function to get the date and time to the second that this order was placed. That is handy if you want to figure out what time of day is your busiest, maybe if you are a retail location. It is important to sometimes store that information. In fact, that is going to be the focus of my next Fast Tips video. I am going to show you how to isolate the time and then figure out what the busiest times of the day are.
But for today's video, we want to chop that time off and get just the date part. That is good to know because if you are doing calculations, like you say show me all the orders for this month, you might be missing some records if you do not do it right. That is the point of this video.
Before we get started, a couple prerequisites for you. First, if you do not know how to use a calculated query field, go watch this video. Also, go watch my Date Math video so you understand how dates work in Microsoft Access. One day equals one, for example. Finally, I use the ISO date format, which looks like this right here: year-month-day. That way it works with everybody around the world, because I have students in pretty much every country there is. This way you are not confused as to which date format I am using, so I decided to stick to this one.
Go watch all three of these videos. These are free. They are on my YouTube channel and on my website. Go watch them and then come on back.
So, here I am in a copy of my TechHelp Free Template. This is a free database. You can grab a copy on my website if you want one. In this database, I have an Order table with an Order Date field.
I store the date and time when I collect this information because, like I mentioned earlier, I want to know what time of the day the order came in. But when it comes to doing calculations, if I just want to see all the orders from January, I cannot just say show me everything between January 1 and January 31 because the default for a date value is that date at midnight. So you will not see this record.
Let me show you what I mean. Let's go make a query out of this. Create Query Design. Let's bring in the Order table, and I will bring down the OrderID and the OrderDate. I will take a peek so we see what we have there. There they are.
Now, if I come down here and put a criteria on and I say show me between 2022-01-01 and 2022-01-31 and I run that, I am only seeing two orders. The other one, which was January 31, if you look at the table, January 31 at 4 PM, that is greater than the 31st at midnight. That is how dates work.
If I came down here and incremented this a day and I said between that and 2-1, and I run it now, there is that order. But if you do that with the BETWEEN keyword, watch this. If I have another order in here that happens to come in at midnight on January 1, now when I run this, that order shows up. So you do not want to do that with the BETWEEN keyword.
The other alternative is to use an inequality. If I come in here, let me Shift+F2 so you can see it better, you could say greater than or equal to January 1 and less than February 1. This will work, but that is a lot to remember. Because now if I run this, there is the right data. But you have to remember to use an inequality all the time and you have to remember to say greater than or equal to the first date and less than the second date. That is a pain.
So let's just get rid of that altogether. The easier way to handle this situation is to just simply make a second calculated field and chop off that time. So I will come over here, and again, I will zoom in for you, Shift+F2. I will make a new field called OrderDateOnly: and that is going to be equal to the DateValue of OrderDate. It is that simple.
The DateValue function says give me any date/time value, and I am going to chop off the time and just give you the date. Now when I run it, there you go. There is your new field that has just the date in it. You can use this for calculations between two dates so you do not necessarily want that time involved. I can say between 2022-01-01 and 2022-01-31 like that, and run it, and there you go. You get exactly the records that you want and expect.
This is especially critical with beginners. Beginners do not always think about that. They do not know that, and they just think that if they type in that date they are going to get all the records on that date. That is not how Access thinks.
Now, are you ready for some bonus material? Here is a little bonus for you. If you want to make this into a parameter query, there is a little bit of extra work you have to do. Watch this. If you change this to a parameter, like [Start Date] and [End Date], now watch what happens. If you do not know what a parameter query is, whatever you put inside of these square brackets, Access is going to ask you for. I will add a link to my Parameter Queries video to the links section down below if you want to watch that.
Whenever you make this a parameter query, if you run this, now it is going to ask you for these start dates. If I type in 2022-01-01 and 2022-01-31, you get no records. Why is that? What happens is Access converts that to text. It converts that to text. So if you want to get away with this, you have to convert these back to a date.
We can do that with the CDate function. CDate is a date conversion. All right, do that. Now, if you want to run it, you can get away with just typing in 1-1, it will assume the current year, and 1-31. There you go.
Someone is beaming in. Hold on. I love that. That is my cuckoo clock. My server reboots once an hour. That is what plays before it does that on top of the hour.
There is your bonus for today. This has been your Fast Tip, and I hope that this has taught you something. I hope you learned something today.
If you want to learn more about dates and times, I have two classes, each about an hour and a half long, that cover my comprehensive guide to Access functions, and these two classes cover date/time stuff. Everything you want to know about date and time is covered in these, 27 and 28. That is part two.
I also have the Advanced Date and Time Seminar that covers all kinds of extra stuff: all of this stuff, lots of stuff, and even more stuff, and tons of functions. For example, how many network days are there between two dates, calendar tables, reminders, all kinds of things.
If you work with a lot of dates in Access, this is what you want: the Date/Time Seminar. I will put links to all this down below.
Hope you learned something today. As the Riddler said, if you kill them, they will not learn anything.
I will see you next time. Have a good one.Quiz Q1. What is the primary purpose of the DateValue function in Microsoft Access?
A. To extract the date part from a date time value, removing the time portion
B. To calculate the time difference between two dates
C. To format the date into text
D. To add days to a date field
Q2. Why might using the Now function to store dates in an order table be useful?
A. To always store today's date at midnight
B. To know the exact date and time an order was placed for later analysis
C. To prevent duplicate orders
D. To automatically assign order numbers
Q3. What issue might arise if you query a date range using the BETWEEN keyword on a date time field?
A. Only records at midnight for each date are returned
B. All orders from the entire month will always be returned
C. Dates with a time after midnight may be excluded from results
D. Query errors occur and no data is returned
Q4. What is the recommended alternative to using BETWEEN for date range queries with date time fields?
A. Use an inequality: >= [start date] and < [end date + 1]
B. Use only the start date in the criteria
C. Use the LAST function
D. Filter on the order number instead
Q5. To simplify working with just dates in queries, what approach does the video recommend?
A. Creating a new calculated field using DateValue to remove the time portion
B. Changing all date time fields to text
C. Removing all time data from the table
D. Sorting the table by date
Q6. What does the DateValue function return when applied to a date time field?
A. Only the time portion
B. The date portion set to midnight
C. The text representation of the date time
D. The sum of the date and time values
Q7. Why do beginners often get unexpected results when filtering for a specific date in Access?
A. They do not realize Access includes the time part in date time fields
B. Access changes all queries to text by default
C. Access only looks at years, not months or days
D. Access does not support date queries
Q8. When converting a query to use parameters like [Start Date] and [End Date], what issue arises?
A. Access treats parameter inputs as text, not as dates
B. The query cannot be run at all
C. The time portion is always preserved
D. It only works if you use BETWEEN
Q9. What function should be used to convert a parameter input to a true date value in Access queries?
A. DateValue
B. ConvertToDate
C. CDate
D. FormatDate
Q10. What does the video recommend for learning more about dates and times in Microsoft Access?
A. Watch basic Access introduction videos
B. Take the comprehensive Date/Time classes and Advanced Date and Time Seminar
C. Look up solutions on unrelated programming forums
D. Only use built-in Access wizards
Answers: 1-A; 2-B; 3-C; 4-A; 5-A; 6-B; 7-A; 8-A; 9-C; 10-B
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 explains how to use the DateValue function in Microsoft Access to extract just the date portion from a date/time field. This is especially useful when you have fields that store both the date and time, like an order date, but you only need to work with the date itself for your analysis or reporting.
I often use the Now function to capture the exact date and time when an order is placed. This detail is helpful if you want to analyze order patterns by time of day, which is something I will cover more thoroughly in an upcoming video where I focus on isolating the time part and finding peak hours. For now, we are concentrating on how to drop the time and keep only the date.
This is important because, when you run queries to find all orders in a particular month, you might unintentionally miss some records if you do not handle the time portion correctly. For example, simply using "between" criteria for dates may leave out records that have a time other than midnight on the last day.
Before you follow along, you should understand calculated query fields and how date math works in Access. I recommend watching my introductory videos on these topics, as well as familiarizing yourself with the ISO date format (year-month-day), which is what I use to avoid confusion for viewers worldwide.
In my demonstration, I use a sample database with an Order table that records both the date and time of each order. Let's say you want to see all orders from January - if you filter using "between January 1 and January 31," you might miss orders made on January 31 if the time is later in the day, since Access stores that as "January 31, 4 PM," which is after "January 31, midnight." Using "between" alone does not always catch all the data you expect.
One way around this is to use inequalities in your query criteria, such as "greater than or equal to the start date and less than the next day." This works, but it is easy to forget and can be a nuisance to set up each time.
A more straightforward solution is to add a calculated field to your query that uses the DateValue function on your date/time field. DateValue takes a date/time value and returns only the date, stripping off the time component. With this new field, you can apply the typical "between" criteria and get only the records you want, regardless of the time stored in those fields.
This technique is especially helpful for beginners, who may not realize that Access evaluates dates with a time component and that simply entering a date may not pull all records as expected.
For those who want to take it a step further and use parameter queries, where Access prompts you for start and end dates, there's an extra adjustment required. When parameters are used, Access may interpret the inputs as text, which can lead to getting no records returned. To correct this, you need to convert the parameter back to a date using the CDate function in your query. This ensures your criteria compare actual date values, not strings.
I also mention some additional classes and resources on my website for those interested in mastering date and time functions in Access. These cover everything from basic functions to advanced topics like working days calculations, reminders, and calendar tables.
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 Using the DateValue function to extract the date portion from a date time field
Problems caused by time portions in date range criteria
Demonstrating incorrect results when using BETWEEN for date ranges
Correct date filtering with greater than or equal to and less than criteria
Creating a calculated query field for the date only
Implementing DateValue in a calculated field in a query
Using the calculated date-only field for accurate date range queries
Handling parameter queries with date inputs
Resolving parameter query conversion issues with the CDate function |
