Convert ISO Date Time
By Richard Rost
18 months ago
Convert Full ISO 8601 Date Time to Microsoft Access
In this Microsoft Access tutorial, I will show you how to convert an ISO 8601 date-time value into a usable format for Access. Learn to split the date and time components, handle time zones, and create calculated fields using functions like DateSerial, DateAdd, and string manipulation features.
Kenneth from Yantis, Texas (an Expert Student) asks: I import Amazon Kindle Data to get a list of books that I have. The fields for dates come in with all those numbers and such that are never needed. Is there a way of formatting that data to just show the proper date?
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, ISO 8601 conversion, date-time formatting Access, split date-time Access, extracting date Access, time zone conversion Access, date serial function Access, string functions Access, left mid right functions Access, Access query date conversion, UTC to local time Access, advanced query design Access, calculated fields Access, querying date-time values Access.
Transcript
Today we're going to talk about how to convert an ISO 8601 date-time value to an actual date-time value that Microsoft Access can work with. I'll show you how to split it apart, get the date, get the time, and then we'll talk about that little time zone bit of information at the end over there.
Today's question comes from Kenneth in Yantis, Texas, one of my expert students. Kenneth says, I import Amazon Kindle data to get a list of books that I have. The fields for dates come in with all those numbers and such that are never needed. Is there a way of formatting that data to just show the proper date? Well Kenneth, all those other numbers, you got the date over here right then the T stands for there's your time and then the stuff on the end over here is your time zone information, so for example this 0000 indicates it's UTC universal time, otherwise known as Greenwich time, and if you were in let's say Eastern Standard Time this would be minus 0500 for minus 5 hours behind UTC. So in order to convert this into a date-time that Access can work with, we're going to use some different functions.
Let's talk about the prerequisites for today's video. First up, since Kenneth is an expert student, we're going to keep this at the expert level today which means there's no VBA required. Now if you're not familiar with ISO dates, I'm on a mission to convert the whole world over to the ISO date format. It's the only one that makes sense around the world. Right? Year, month, day. Okay? So watch this video if you want to learn more about that. Watch this video to learn more about ISO dates specifically in Access. I also have one for Excel too if you want to watch that.
Now all my other videos so far have focused on just dates but ISO also specifies some different time formats too, and we'll talk about that in today's class. You're also going to need to know how to make calculated fields and queries, how to use the different string functions like left, mid, right, and those things. We're going to use date serial. That's where you can take a bunch of the components of a date like the year, the month, and the day and put them together into an actual valid access date. We're going to use date add when we get down to the time zone stuff so we can add or subtract time to the date. We're going to use type conversion functions just to see long today to convert a string to a long integer or a number. And I know this is the last one, the if function, the immediate if which is basically an if-then statement inside of a function. And there might be more as we go along, I'm not sure. This is the ones I just wrote down. So if you haven't watched any of these other videos, go watch those first, and then come on back. We'll wait for you.
All right, so when it comes to dates, it's pretty unanimous that the date format is yyyy-mm-dd, or you might see it without any separators, without any dashes. Usually, if you're time-stamping a file, you might name the file without dashes in it. But generally, the date is pretty uniform. The time, however, I've seen come in a variety of ways. So I asked chat-GPT, I'm like, is there an official standard? Basically, not really. You've got all these different official standards out here. Sometimes you'll have a T in there to differentiate the date from the time. Sometimes you won't. Sometimes there will just be a space there. In any event, there's always something there. You might have a Z on the end, which stands for Zulu time. Zulu is basically another word for UTC, or Greenwich Mean Time. Sometimes you'll have plus and then a time like that for the time zone. Sometimes it'll just be four digits, which is what Kenneth got. And there's other different variations down here. Basically, this is going to be a lesson in just, you have to deal with what they give you. Usually when you're doing an import, I'll give you a look at Kenneth's data here, it's all uniform. So you just got to look at these rules and know how to deal with it, okay? And this might be different between different data sources that you work with. I wish there was one just standard format that everyone used, but unfortunately when it comes to the time part, there isn't.
All right, so let's start off by setting up a table to store this data. So table design, I'll just call it an ID field and then we'll make ISO date-time our field. Now it's going to be a text field as it comes in. In order to import this as it is from your data source with that T in there, you're going to have to import it as text then we'll convert it. And I'm just going to do the conversion in a query. If you want you can then take that data and then write it back into the table as a date-time value. Maybe we'll do that later. I don't know if I feel like it. Well let's just save this as my ISO T. All right primary key sure and now let's get some data. Now I'm going to get the data that Kenneth has and I'm just going to copy and paste it from here in the OCR. I'm going to use my screen capture tool. I like to use hyper snap. I'm going to grab this text right here just like that snip it to my clipboard. I'm going to go to the Google machine and click on this little thing here the search by image and then paste in what you just copied and look at that come down to the bottom here hit text and then select all text. Now that should be in the clipboard after you hit copy. Copy it and now I'm going to come back here and I'm going to highlight this column because all I have is a column of these. And then paste it. Boom! And there you go. There's all your data. See that? That's the key. I showed this in another video too. And if it's two fields, you can select them like that. If you've got like first name and last name, you have to tab delimited. All right. So now I've got all that data that Kenneth had right in my table, all right, in a text field. Now we're going to do some work with a query. So we'll save that. Actually, this part helps if you can see the data that you're working. So we're just going to leave that open. Create query design. And let's resize this just a bit, just so I can see some of that. Let's bring in that ISO T. There it is. And bring in the ID and the ISO date-time. And this doesn't have to be there.
All right, anyways, down here is where we're going to start taking apart this date. We're going to start with the year. Now, the year is the left four digits. So I'm going to call it YR, that's the name of my field, and that's going to be the left of, you know what, I don't want to have to type in ISO date-time every time, so let's alias this guy. Instead of ISO date-time, we're just going to call it D for date. Let's call an alias. That's how I can now say if I run this now, that guy's called D. So now in all my other formulas, I can refer to it as just D. That makes much more sense. So here, we're going to say YR, and I'm making him two digits because he got month and minute. Just bear with me. So this is going to be the left of D, 4. That's how you get the year out. So now you run it. There's your year. Makes sense.
All right, the month, M-O. Now we got to use the mid function. We got to go in 6 and across 2. So there's 4, 5, 6 is right there. So it's going to be the mid of D, 6, 2. And the same thing with the day is going to be the mid of D, 8, 2. You just got to go across 8 and get 2 characters. And then when you run it, there we go, year, month, day. All right, we're cooking with gas now.
Now, if all you want is the date, like Kenneth, if you don't care about that time, you just want the date that the record is from, then wait a minute, I'm wrong here, aren't we? Or what do we do? That should be nine. Yeah, it's nine. My bad, my bad. Check your data folks when you're doing it. Six, we got about that dash in there. There we go. That looks a lot better. Notice I was getting the dash because I did eight, which is that. All right. Check it at each step of the way.
Now if all you want is just the date, you could put it together now using date serial. Right date serial is how we build a date from the components. So right here let's call it new date. That's going to be date serial and what is it? YR comma MO comma DA and Access will convert those from strings to numbers for you. Boom, there you go. And you can tell it's a date value because it lines up on the right side of the cell. See that? And a little date picker pops up. But we want to continue this. We want to get the time and then work with that time zone information as well. I want to use all of this thing.
And we'll do more in tomorrow's video. So tune in tomorrow, same bad time, same bad channel. And if you remember you can watch it right now because I'm going to keep recording and you'll have it in just a few minutes.
But that is going to be your TechHelp video for today. I hope you learned something. Live long and prosper my friends. I'll see you tomorrow for part 2.
TOPICS:
Converting ISO 8601 date-time to Access date-time
Splitting ISO date-time into components
Extracting date from ISO date-time
Handling time zone information in ISO date-time
Setting up a table to store ISO date-time data
Importing ISO date-time data as text
Copy-pasting data into Access
Creating and designing queries in Access
Using aliases in query design
Extracting the year from ISO date-time
Extracting the month from ISO date-time
Extracting the day from ISO date-time
Building a date using DateSerial function
COMMERCIAL:
In today's video from Access Learning Zone, I will show you how to convert ISO 8601 date-time values into a format that Microsoft Access can work with. We’ll start by splitting the date and time, then handle the time zone information. This covers functions like left, mid, date add, and type conversion. By the end, you'll be able to easily import and format your date-time data. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is required to import ISO 8601 date-time data into Microsoft Access before converting it?
A. Import as a number field
B. Import as a text field
C. Import as a date-time field
D. Import as a memo field
Q2. Which function combines the components of a date (year, month, day) into a valid Access date?
A. DateValue
B. DateSerial
C. DateDiff
D. DateAdd
Q3. If the ISO 8601 date-time string is "2023-10-15T12:30:00-0500", what does the "T" signify?
A. Beginning of the date
B. Time separator
C. Time zone information
D. End of the string
Q4. Which function would you use to extract the first four characters from a string in Access?
A. Mid
B. Left
C. Right
D. Split
Q5. What is the ISO 8601 format for date?
A. yyyy/mm/dd
B. dd/mm/yyyy
C. yyyy-mm-dd
D. mm/dd/yyyy
Q6. When dealing with the time zone information, which function is suggested to adjust the time in Access?
A. DateAdd
B. DateSerial
C. DateDiff
D. Now
Q7. In the context of an ISO 8601 date-time string, what does "Z" stand for?
A. Zulu time (UTC)
B. Zero hour
C. Zoned time
D. Zero seconds
Q8. For extracting a substring from the middle of a string in Access, which function should be used?
A. Left
B. Right
C. Mid
D. Substring
Q9. If an ISO 8601 date-time string is "2023-09-10T08:45:00", which part of the string represents the time?
A. 2023-09-10
B. T08:45:00
C. Z
D. 09-10T08
Q10. What is the best approach to handle varying formats of date-time strings from different data sources in Access?
A. Ignore the variations and use default settings
B. Manually adjust each string in the table
C. Use string functions like Left, Mid, and Right to standardize them
D. Convert all data to text and process it in Excel
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-A; 7-A; 8-C; 9-B; 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.
|
