Convert ISO Date Time 2
By Richard Rost
18 months ago
Convert Full ISO 8601 Date Time to MS Access, Part 2
In this Microsoft Access tutorial, I will show you how to convert a full ISO 8601 date time value into an actual Access date time value. We'll cover the use of the mid function, time serial function, and managing time zone adjustments. This is part 2.
Members
In the extended cut, we will walk through how to use an update query to add the UTC date time value back into the table. I will also show you how to create a VBA function for converting single dates, which can be used on forms or reports.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Update
- Gold Members: here's a link to a page in the Code Vault that contains some functions to convert a valid Access date/time value into an ISO-formatted date string. They're written for ASP, but you can easily convert them to Access VBA.
Recommended Courses
Keywords
TechHelp Access, ISO 8601 date-time conversion Access, DateSerial function Access, DateAdd function Access, split date and time Access, handling time zones Access, string manipulation Access, TimeSerial function Access, add datetime values Access, UTC conversion Access, adjusting time zones Access, calculated fields Access.
Transcript
This is part 2 of converting ISO date time values to an actual Access date time value. So, if you haven't watched part 1 yet, go watch part 1 and then come on back. Alright, we left off yesterday by getting our table set up. We got our query started. We got the year, month, and day split off and now we've got the date part of this guy here, right, with the date serial. Well, now we're going to do the same thing with the time values.
Alright, we can still use the mid function to pull out the hours, minutes, and seconds, and then, just like there's date serial, there's also a time serial function that does pretty much the same thing. You get the hour, the minute, the second, and it builds a time for you. Well, isn't that nifty? Let's come in here, design view, yes, I said nifty. This will be basically the same thing. I'm going to shrink this up just a little bit more so we've got a little bit more room in here.
Alright, so this is going to be the hour, which is the mid, d, 12, 2 if I did my numbers right. And that looks about right. Notice these first ones don't have any times on them. They're all zeros and these guys look like you know 220904. Okay, so we're good with that so far. Now we can likewise build the time value with the time serial function. And while we're at it, let's save this query. I'll call it my ISOQ. Forgot to save it last time. I'm gonna shrink these all up down here so I got some more space. Just like in Excel, you can select them all and then resize like that and it shrinks them all down to that same size.
Alright, just like we got new dates over here, let's make a new time. So new time is gonna be, I'm gonna zoom in so you can see it, shift F2. It's gonna be time serial hour, minute, second, just like that. The time serial. I don't have a separate TechHelp video for time serial. It's on my list. I haven't made one yet. I cover it in my Access Expert classes though. I cover all the functions in that, like all of them. We'll talk about that at the end of the video. But let's make sure that's working now. And okay, there's the time. That's the time portion of this. Right, oh, sorry, right here this right 22 9 4 is 10 to this p.m. now right 2200 hours is 10 p.m. oh 9 oh 4.
Alright, now if you want to put this and this together into one thing, what do you do? There is no date time serial function. Well, remember in Access, a day right a day equals one if you do the date math. I got a whole video on date math in Access. It's very good, go watch it. I'll put a link down below. Alright, this guy here. So in Access, one day equals one. A week is seven, okay? Hours, minutes, and seconds are fractions of a day. So an hour is 1/20th of a day and so on. So if you wanna add this to that, you'd literally just add them together to get a new value.
So in Macquarie right here, we'll call it new date, actually let's make it smaller, let's call it ACC date or ACC date time, whatever you want to call it. We'll just call it ACC date. Okay, this is literally going to be new date plus new time. Just add the time value on the date that should give you this that uh... look at its one big time now that that's all you want great if all of your values like all of his values all had zeros let's go ahead and change some of the let's say this one here is in the eastern standard time to go minus five hundred minus 500.
OK. Alright, if one of these comes from, let's say, Pacific time, you'd be minus 8. So minus, and then 800. And believe it or not, there are some time zones that are 30 minutes back or forth. So let's do one of these as plus, let me see, I had to look this up, Indian Standard Time is plus 5 hours 30 minutes, so it'd be plus 5.30 in Indian Standard Time. Okay, so plus or minus GMT.
So now we have to look at that as well. We got to pull more stuff out of the data. So there's actually three bits of information I want to extract. First is that plus or minus, okay, so we know if it's adding or subtracting from the time. Then we need the hour, the time zone hour, and the time zone minutes. Okay, so again, three more fields right down here, design view. Let me again shrink this stuff up. Okay.
And yes, you can run out of fields going this way. If that's the case, you can always insert new ones. But yeah, sometimes I got really long queries, and they run off the end over there. Alright, so back here. So next we need the time zone, let's call it the TZPM. It's the time zone plus or minus and that's going to be mid D comma 20 comma 1. That's going to be that guy. It's a plus or minus. And then the hours the D, 21, 2. And then the tz minutes, tzm. You could use mid or you could use right. I'll just use right of D, 2. If you're sure, there's nothing else on the end here, like a z for Zulu or whatever. Any of that stuff. OK? All right. Run it. So now we've got our time zone information right there. OK.
Now, right now since we extracted these out of text just like these ones, okay, these are string values. I'm going to convert these over to long integers. Now, I didn't bother converting these because I knew we're going to be using them inside of time serial and date serial. So we didn't really have to. The function took care of converting that. But these I want to convert manually. So design view and right inside here, I'll just zoom in so you can see it, we're just going to put in here CLNG. Normally, I break this up into multiple steps, but that's an easy one. Here's where we do the mid extraction and then we're just going to take that text string value and convert it to a long integer so that we can add it to the actual time value. We'll do the same thing with the minute, CLNG.
So now we got numbers. And the key to telling that they are numbers is they'll now line up on the right side of the cell. See that? Now you know they're numbers. OK? And we lost our leading zeros, which is fine. That's OK. I just need to know how many whole hours that is. Now we can date add this value and this value to our access date value to adjust it for the time zone. This one here, we will subtract 5 hours from that to get the actual value. Now I can do two date adds or I can just do one if I just convert this to minutes. Add the two of them together and then we'll have one value to do a single date add with.
So I'm going to take this guy, multiply it by 60 to convert it to minutes and then just add it to this one. And we can kill two birds with one stone on this one too. Well, we'll make it another field. Alright, total TZM is going to be TZH times 60 in parentheses. Well, you really don't need the parentheses because of order operations. But then we're going to add TZM. I know order of operations says you don't need those parentheses, but I feel like it makes it more readable. And that's the whole point of math.
I know you see all these things on social media, like trying to trick people that don't know BECMI, or not BECMI, what's it, PEMDAS, BODMAS. BECMI, that's a D&D term, BECMI, why was I thinking of that? But you know, the parentheses, exponents, all that, it's an order of operations. Multiplication goes first, so you would only really need these parentheses if that was an addition. But I think it makes it more readable.
So anyways, let's take a look at what we got now. Alright, there we go. So five whole hours is 300 minutes. Eight whole hours is 480 minutes. And this guy is 330 minutes, great. Now I got the value that I can either add or subtract to this. And how do I know whether I'm adding or subtracting? Well, it's this guy. This guy determines whether or not we subtract or add.
So I'll use an if function, if, and yes, I say it goofy like that so you remember it. It's if, with a bunch of i's in front of it. If, not if, if, if, if, if, if, if, if, if. You know how many times people post questions in the forums or send me an email with their code and it's IFF. No, it's IIF, immediate if. Alright, and so I say it like that stupid in the video so you remember it. So you're not like me pulling your hair out because you type it in the wrong way 5,000 times. I'm just as guilty.
Alright, so here at the end, alright, one more calculation. Here we'll call this total TZM2. It's going to be if the TZPM, that plus minus field, this little guy over here right if that equals a minus sign inside of quotes right that's a minus sign then we're subtracting so the value is going to be total TZM times negative one or it'll be total TZM right? Right? If we're subtracting, make this a negative number. If we're adding, leave it as it is. And now we get this. This is minus 5, so we're subtracting 300 minutes. This is plus 530, so we're adding 330 minutes. And now it's simply a matter of adding these minutes to that date with the date add function.
So one more thing and then we're pretty much done. Let's see what we got here. Last one. Alright, so let's call it my UTC date time is going to be date add. What are we adding? We're adding minutes which is N. Remember that's another sticking point for a lot of people with date add and date diff and all that. N is minutes because M is months. N, how many minutes are we adding? It's total TZM2. What date are we adding it to? And I forgot because I can't see it. So what I do in this, I gotta scroll to the left here to see it and it's not in my notes. What I'm gonna do here is just put an X in there because I don't wanna lose all this.
We can copy to the clipboard. Alright, and then I gotta scroll back to the left just to see what that is. Okay it's ACC date. I'm embarrassed but I do that all the time with these really long queries sometimes. Alright, so we'll put ACC date in there. So we're adding minutes. How many minutes? That many minutes, which is this guy to that value. And now our final value should be right there.
Alright, so this guy here, let's see, we started off on November 29th at midnight and we had to go back five hours. So we're in November 28th at 7 p.m. Okay? Same thing down here. We got October 30th at 6:30 p.m. and so we had to go an hour forward to October 31st, just after midnight, looks good. And of course, at this point, I should mention that if these were times that were taken and the person was in that time zone and you want to convert it to universal time, to UTC time, then instead of subtracting, you'll add. You'll just reverse that. So it's all based on however you want the data to go. Are you converting to UTC or are you converting from UTC to that time zone? So either add or subtract, whatever you got to do.
Alright, so now you've got this UTC date time value here in this query. What if you want to get it back in the table? Alright, what if you got, you know, you want to put that date in here in another field and then not have to use the query every time, then you can just delete this data, right, especially if you're importing it. You import it once and then you just copy it over. Well, you can use an update query to do that.
And I'll walk through that in the extended cut for the members. It's not hard. A little update query. I've got videos on my website for using update queries. But I'll walk you through it in the extended cut. And we're also going to create a VBA function. This is nice and all. But sometimes you might just have a date that you got, maybe on a form or a report and you want to just convert one date using a function. So we'll make a little global function you can just use and call it whenever you want. Okay, that's all in the extended cut for the members. Silver members and up get access to all of my extended cut videos and there's lots of them folks. There's hundreds of them.
And I mentioned earlier in my expert classes I cover all of the functions in Access. All the string functions, the logical functions, the math functions, the type conversion functions. I got a whole comprehensive guide. There's the date-time functions, there's, you name it, there's all kinds of stuff. What do we got here? There's, you know, trig functions, aggregate functions, you name it, it all starts in Access Expert Level 25. I forget how many there are, there's tons of them here.
Financial functions, we do a loan calculator. There's, well, I think that's the end of it. Yeah, then we get into charting itself. So access X for 25 I'll put a link to that down below.
And that's gonna do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut. I'll be waiting for you in the parking lot. Give me Mr. Gilmore's jacket. Okay, bye.
TOPICS:
Converting ISO date time values to Access date time
Using mid function to extract time components
Using time serial function to build time
Combining date and time into one value
Handling time zone offsets in date time values
Extracting time zone information
Converting text fields to long integers
Date add function to adjust for time zones
Creating new calculated fields for time zone adjustment
Using IIF function for conditional logic in queries
Final validation of combined and adjusted date time values
Creating and using update queries
Developing a VBA function to convert ISO date time values
COMMERCIAL:
In today's video from Access Learning Zone, I'll show you how to convert ISO date time values into actual Access date time values. Yesterday, we set up our table and started building our query to extract the date. Today, we'll do the same for the time values using the mid function to pull out hours, minutes, and seconds, and then combine them using the time serial function. You'll also learn how to handle time zones and adjust your values accordingly. Don't forget, for a deeper dive and some bonus VBA content, check out the extended cut for members. 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 specific function does the "time serial" perform in Access?
A. Converts date and time to string format
B. Converts text to numeric format
C. Builds a time from hours, minutes, and seconds
D. Formats numbers to currency format
Q2. In this tutorial, which function is used to extract the hour, minute, and second from a string?
A. DateSerial
B. Mid
C. Left
D. Right
Q3. What does adding the "new date" and "new time" together accomplish in Access?
A. It formats the date value into a string
B. It combines date and time into a single DateTime value
C. It converts time values to UTC
D. It subtracts the time from the date value
Q4. What is necessary to adjust the date and time values for different time zones?
A. Including specific date formats
B. Adding or subtracting the time zone hours and minutes
C. Using the DateDiff function
D. Changing the data type to TimeZone
Q5. What does the "CLNG" function do in the tutorial?
A. Converts text string value to long integer
B. Converts date to string format
C. Trims unnecessary characters from text
D. Formats numbers to percentage
Q6. Why is it important to convert text string time zone offsets to long integers?
A. To format the date properly
B. To avoid errors in Text fields
C. To perform arithmetic operations with them
D. To store them in a separate table
Q7. How do you determine whether to add or subtract the time zone offset to the main date-time value?
A. Using the LEN function
B. Using the IIF function to check the plus or minus sign
C. Using the DateSerial function
D. Using the Mid function to convert time
Q8. For converting hour offsets to minutes and then adding them to the main date-time, which function is used?
A. DateDiff
B. DateAdd
C. TimeSerial
D. Format
Q9. When combining both time zone hour and minute offsets into total minutes, why are the operations done within parentheses?
A. To ensure correct order of operations
B. To improve calculation speed
C. To convert the result to string
D. To format the output
Q10. What is the purpose of the "Update query" mentioned towards the end of the tutorial?
A. To convert text strings to integer values
B. To automate data input
C. To update the table with calculated DateTime values
D. To create new tables for different values
Answers: 1-C; 2-B; 3-B; 4-B; 5-A; 6-C; 7-B; 8-B; 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.
|
