Video Transcription

Welcome back everyone, this is going to be our last video for the single table queries part three series. And in this video, we’re going to be looking at some date time data types. There are a lot of different functions and functionality that you can leverage as far as date and date times, I’m not going to cover all of them, I will cover a few and explore a little bit more or have a little bit more and actual written notes, as well as links to official documentation for you to read through as well if you’d like to explore in a deeper sense. So dates, right. Date itself contains date only. So everything from 111 to nine into 912 31. So that’s the max range. So you know, if Microsoft still exists in the year 2000, then they’ll have to figure out a new way to store dates, in the sense. But we also have the time format, measured in a fractional second scale from zero to seven. And I’ll show some examples of what this looks like here in just a bit.

But we also have Date Time to which combines date and time. And we also have date time offset, which adds a two byte timezone offset to the date time object. So date time offset is the same thing as date time, except that we also have a timezone attached to it as well. There’s also other just like with strings, there’s a huge number of functions that we can utilize here. So sis, date, time, sis, date, time, offset, sis UTC daytime, are all functions that we can utilize to pull out the current date and time or time off of the server where our database is actually being hosted. We can manipulate that timestamp, and a variety of ways pulling out parts of the dates, the day, month or year, we’ve already we already utilize the functions month and year already. And then we can actually change the time offset so we can change the timezone if we need to. We can also convert things back and forth with specific date times that we’ve already seen with the Convert function last time, where I changed the format of the date of how it actually is shown. We can also do some manipulation, adding to and adding to a date.

So date add, we can find, we can subtract dates using date diff. So like how many days are between data a and data B, that can be a very useful functionality to do. But nonetheless, let’s take up, take a look at a couple of examples. And again, I’m not going to showcase all of them here I will showcase some and then I will leave the rest for you to explore and reading the notes. So here I have a single variable date time now. And if I and again, I’m using the date time offset data type, because I want to keep the timezone. And I can showcase that what it looks like here. So there’s my current timestamp. Now, I can work with us in a lot of different ways. So date time offset is, by default offset seven. That’s the so it’s offset by seven hours. But you can change the offset through parameters if you like. So that offset, you can specify a number zero on a scale on where’s my cursor here, so you can insert the number here as a parameter of the number of offset or the number that you want to offset.

So let’s use our date time and our variable now to expand on that. So notice here too, I have to in order to use a variable in a statements and SQL on the variable must be declared so I cannot actually add. Now I can’t declare now in the same declare statement that I actually use now. So I have to declare it first and then I can use it in subsequent sequence statements. So that’s why I’m using two declare statements here, one to declare now, and then another one that utilizes that date time. So all I’m doing here with these is I’m actually taking the date time and stripping out only the date, the time, time with milliseconds, and then the full time here. And so you can actually change the offset here. So by default, right, the offset of the scale is seven. But we can actually change the scale as we go. There we go, let’s go ahead and actually execute this code. And so you can see the different formats, and I would recommend you try this out on your own computer. Because, again, you can kind of see the difference. And you can kind of play with the different scales and things like that.

So here is our date. State, as I pull the date, I just convert the date time offset to just date, the date type, and then time no fractions, so time was zero. So zero milliseconds, then here, I have scale three. So basically, time with milliseconds. And then this is the most detail that we can actually get with our, with our date, time or our time datatype. Not that you will always need to go that far down. But that’s about as Max as you can go as far as specificity with the current time in seconds. But note here, if you only have like say this is time to the to the third, so I three decimal places over 832. And then the rest of it gets padded with zeros. But if you have seven, it actually does go full out. And it doesn’t just slice off what’s at the end, right. So I can also show I also want to show the date time, too. So look, showcase that. So here just to showcase what date time two looks like compared to the previous one. So here we go. But now we have no offset, so Date Time to but no timezone information, no offset, in terms of hours go. And we can convert the time implicit conversions like I did before.

So I converted the date time offset to a date form a date type date variable, I can do similar thing with the date to a date time too. So running this code here, there is an implicit conversion that happens when I actually sorry, scroll up there, there is an implicit conversion that happens here with the date. So I declared date as a sis date, time, and then I can take that date and then go to date time to which then is just implicit conversion. But note that the date, a date, variable data type has no information for time. And so the time here is all zeroed out because there is no information. So there is implicit conversions between the two. But just note if you go from a date to a date time, the time is going to be zeroed out. But if you go from a date, time to a date, the time just gets cut. And so it is a little bit easier to go from Date Time To date, you do lose information. But nonetheless, this is some of the behavior that you’ll see in converting from one data type to another.

The default date itself is not always obvious as well. So very similar to the code that we just ran. So here we have time and date time to so this is date time. And I basically, I am I’m going from just kind of highlighting some of the auto conversion behavior that happens here. I’m going from the time on my current system so 124 to a date time object, which is okay. But look at the date that it defaults to right, it defaults to one 119 100. So the date itself, the date value is not always obvious when you’re going when you’re, when you’re converting up, right, so you’re going from a time to a date time, instead of the other way around. So just be careful when you’re working with Date Time objects there. Now, this applies as well to default time zones, and things like that. So a lot of times, in a lot of cases, it’s very good practice to specify specific time zones. So those are the primary things that I wanted to show. There are a lot of other date time functions available for you to use. So, like partitioning the date out, getting we’ve already seen like year, month and day is a new one. We can do pull out specific names, the name like for example of a month, or we can also add and subtract dates as well. So I’ll include a lot of examples for you to look at and execute on your own inside of the notes, but for the most part, that will conclude our talk on data types in SQL. I will bring data types up again, when we start talking about designing our own tables.