Single Table Queries Part 3

Video Transcription

Welcome back everyone. In this video series, we’re going to be taking our last look at single table queries, and primarily introducing some more operations and expressions that we can utilize as part of them, as well as a little bit of discussion on variables and different data types that we can work with. But before we work with that, let’s quickly review what we covered last time. So these are all of the SQL statements that we have looked at so far. And just as a refresher, right, remembering that our SQL query is not executed from top down, there is a particular processing order that we have to that the query adheres to, even though we are required to list the query in the particular order that’s shown here on the screen. So logically, our data is processed. From the from clause, we’re pulling the data from our tables, we can filter the rows, the where clause, group them, then filter those groups with the having clause, then we project or we project to our columns that we are wanting in our results.

So that’s the SELECT clause, then we, if distinct is there, that’s where distinct will happen, as well, so we get unique rows, then we can order those rows by the specific columns that we projected. And then if we include top top happens there after the order by the top is again, unique to SQL Server. Otherwise offset and fetch actually happens alongside the order by operation. And the offset fetch is just like the top as you remember, though, offset is unique, where top does not actually have the ability to offset a certain number of rows. And offset again, is also part of the ancy standard for SQL. But for this video, let’s take a look at some new stuff.

First off here, looking at some predicates. So these predicates are some expressions that we can actually work in, to give a little bit more life, or at least, a little bit more expression, to what we can actually filter our, our results for each of our queries. So we’ve got in between like, and a variety of other things, or there’s also a variety of other predicates that we will actually chat about throughout the semester. But most of these sounds and behave exactly like they’re actually listed here. So in is going to see if a, the value on the left is in the set on the right. So is so if we have a list of numbers, let’s say 510 15 is five in that set would be true, but negative one is not in that set. So very similar to the in operation that you see in Python. But nonetheless, very similar to what we see with tween. So check to see if a value is between a certain range, these boundary values are inclusive, so is five between five and 10. Yes. So So is five less than or equal to 10 Less than or or five greater than or equal to five less than or equal to 10. So between those two boundaries, inclusive, like is going to evaluate whether or not the left hand stream is like the specified pattern. So this is more so like a regular expression.

All of these predicates by the way can be negated. So not in not between not like, which expands what we can actually do with them. But let’s take a look at a couple of examples here. So here are predicates can be used mostly in place of where we would see like Boolean expressions and things like that. So here I’m selecting the order order date, and customer ID from the Orders table, where the customer ID is in this set. So the this, the set is denoted by this tuple the parentheses here 316 and 147. So this is long hand to if you wanted to do this with just a Boolean expression we would do customer ID equals three or customer ID equals 16 or customer ID equals 147. So the end operator duration helps us condense what would otherwise be a longer or long winded Boolean expression into something that is very succinct and easy to read. Now, we can also do a between clause here. So let’s take out our existing where replace it with this one here.

So give me all the orders where the order date is between 2016 One, one and 2016 131. So this is very much like our statement that we had in a previous video where we had where order date is less, or where order date is greater than or equal to 2016. One, one, and order date is less than or equal to the second date. So this is a more succinct way to do a range of values. So let’s go ahead and execute this. And so this gives us all of the orders that are in January of 2016. So the like is a little bit more difficult to actually show. In the notes, I will make sure to link to the documentation. So you can see all of the different pattern patterns that you can actually create for the regular expressions for our like operation or like predicate. So here, select C dot star. So this is give me all of the columns from table C, where customer name is like, tailspin. So tailspin, it’s going to it must start with tailspin.

But then the percent sign is I don’t care what comes after this. So tailspin, whatever, right, so let’s go ahead and run this. Oops, sorry, I had that highlighted that need to run like this. There we go. So Does that tickle took a little bit longer to actually execute because the like operation, the regular expression is a little bit more expensive, especially if you have a significant number of rows here. So we actually have 201 rows here, but we had to compare all of the different customer names. So we have Tailspin Toys, Tailspin Toys, so on and so forth. Where we filtered out all of the customers that started with tailspin, we can be a little bit more lacs with this. If we wanted to say well, give me all the customers that deal with toys. And so we can do percent sent space. Note here that the spaces are relevant. So they are considered. So give me all of the customer names that have something that starts with something has a space and then toys and then space percent, I can make this less strict by taking out the I don’t think there’s anything else in here.

Let’s double check our messages. So 402 rows that have the word toys in it surrounded by a space. And I believe we’re on this here. If we take a look at our messages, again, still same 402. But the this, the specification is a little less strict, right? So give me all the customer names that contain the word toys inside of them. Now this is case sensitive and this case, so do be careful about that. There are more like wildcard characters out there. So if you wanted to say like, give me everything that says that has oil in it, we can actually run that and get similar results there. So that T or that underscore is going to be a wildcard but it is a single wildcard, right single wildcard. So let’s take a look at a couple more examples of some things we can add into our our like predicate, right? So it does support a single character wildcard. So the the percent sign is one or more. But if you only wanted to substitute a single character, if you only wanted a single character there, you would use a underscore. So if you wanted anything, everything that started with some character, and then Ale, you get everything after that.

So that can be pretty useful, then we can also do ranges. And so we have this query here, this will all be in the notes for you. This query here actually polls and, or compares for a phone number. So that tab that down here, so it fits on one screen. But give me all the customers with phone numbers where their phone number is not like this. So we have basically giving me all the customer phone numbers that don’t have an area code of 215. Okay, so if I execute this, again, sorry, let me um, highlight that. So if I execute this, we get 626 rows. So these are all the customers that don’t have a phone number that starts with 215. But I can take out the knot here. And that gives me only the customers that start have a phone number of 215. So that’s how the knot works well, pretty well here. So we covered the multi character wildcard, which is the percent sign single character wildcard, which is the underscore. And then we have ranges and sets which are supported there.

So that’s the square bracket and the zero through nine, we can also do a through z, or we can do like something like 0123. If you don’t want to range inside the square brackets, you can only you can specify a specific set of numbers or letters in there, which helps quite a lot. But that pretty much concludes most of what we can do with predicates. Now I didn’t cover all of the expressions that you can use with like and all of that. But again, I will link to the documentation and have some more examples written up inside of the notes. But again, these predicates are very useful when trying to filter out results as part of your query anywhere where you use a Boolean expression. So now let’s take a look at some more examples for operators. So we’ve looked at a lot of operations and expressions that we can do so far in SQL. But just as a quick little coverage here, all of the different operators that we have.

So we parentheses, multiplication, division, sign, mod, all of these things, very similar to what the standard order of operations is, for your Python and Java code, where Boolean expressions and mathematical expressions so we have parentheses multiplication, all that all that for is all of your boolean expressions, followed by not, and and then all of our predicates, and then equals as the assignment operator, which will become more important when we start talking about variables here and a little bit. So that’s just one part where the equal sign can be a little bit confusing in SQL. We don’t have the double equals we just have the single equals which is both used as assignment operator in certain contexts, and the Boolean comparison operator equality and other contexts. So just be careful when you’re using that. But the notes will have some examples to show operator precedence. I’m not going to show those quite yet in the video. Next, we’ll take a look at some more expressions that we can utilize as part of our sequel.

CASE

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at the case statement in SQL. So the case statement is very, very much like the switch statements in Java, although in Python, they don’t have the language to have a switch statement. But just like a chained if else clause. So in its simple form, it’s going to compare scalar values, so like numbers and things like that, and return a value with its first match. Now, there is also a different form, which evaluates a predicate looking for the first match for that predicate. And the first match is identified when the predicate turns true. And now, there’s a lot of useful functions that we can utilize as part of the case statements, which include is no coalesce if and choose. So we won’t necessarily use all of these, but some of these are quite useful. So is Knowles kind of self explanatory finds the first no value. And then coalesce is going to evaluate arguments in order. So whatever your expression is, and then all of the columns or whatever that you include there, and it’s going to evaluate all of those in order and find the first one that does not have initially evaluate to know.

And so choose, then is going to, which we don’t have, I don’t have a lot of examples of, but choose is going to return an item from a specified index from the list value. So so if I have choose and then choose one, from XYZ, this becomes a little bit more interesting when a value one value to value three, so on and so forth, is not hard coded, but a result of a column or something like that, that becomes a little bit more interesting. So you can at face value choose doesn’t offer you a lot, but it can be very, very powerful. In the in a certain context. We also have if, which is an inline if statement, so if this Boolean expression is true, do the true value otherwise include false, this is very similar to how actual and if statement works in Excel if you’ve ever worked with if statements in Excel, we also have nolloth which is very similar, similar process there. And out of these though, coalesce is the only function that is standard is no if choose are all things that are included in the SQL language as defined by Microsoft SQL Server. So here is an example of a simple case, or a simple case statement. And it’s simple form. So we have all of our so we have our case statement syntax here. So case and so that is the end of it.

So case and so this is basically switch on state province code or if state province code. So case state province code and then when state province code is a k, then crazy fair, when state province code is Kansas, then the place to be when P R US territory when the I US territory. All other cases will be sales territory. So let’s go ahead and run this bit. And we’ll look we’ll explore the the what if part here in just a moment. So we can kind of see already a few of these right? Here’s Alaska crazy fair. And let’s scroll down and find Kansas. There we go. Kansas is the place to be. So this is a really nice, easy way to translate a column into something that is a little bit more user friendly, if that makes things easier for your users. Now what happens when I exclude our else clause? Well you had lots of Knowles Right Knolls, because we only have we only have when state province code matches these four, these four conditions, then we have a value that replaces the state province code. Otherwise, when it doesn’t match any of these, we don’t actually give a value to be put in, we don’t actually give a value to be put in its place, everything would just show up as No, then because the absence of value is no.

So this is the kind of tricky part here we want to be careful there. So if you don’t have an else clause, be aware that a null value will be placed for things that do not match any of your cases, in your cases statement. But this as it is, is in its simple form, meaning that the cases that when a KK SPR, so on and so forth, is a in its simple form. So state province code, it does actually have to search anything. When it’s this, it’s this one, it’s that it’s this, it’s not actually searching in the column. But we can do a different form. So notice, in this syntax here, my state province code is up along with the case. So case, state province code. But we can have something that looks like this same exact functionality. Same exact functionality. But state province code is here. And now instead of actually using the state province code, we’re actually using the sales territory, right? And stead of comparing the states, right. So this is kind of the benefit of using the search form of a case statement, we can actually change which columns we actually compare in each of our cases. So that’s the benefit of the benefit of and the difference between the two, the simple form is going to look and compare the cases for the same column, right?

The same value they so case, XYZ, and it’s going to compare that XYZ to each of the each of the when clauses, right? Otherwise, and the search form, we’re only we’re going to x, we’re going to look at each of our one clauses, whichever one is whichever one is true first, and only and we can actually mix and match, right? We can use different columns in different cases. So that expands the flexibility quite a bit for our cases. So different forums here. So when and where and why would I care about a case statements? So here is a one common need? So a very useful tool to replace values in a column when there are no. And so here’s a case case when the order comment is not null, then keep the comment, right. So if a comment exists, I want to keep the common in. But when there is no comment, meaning the comment is No, instead of showing the user No, I’m actually going to put the the word no comma or the text there. No comment. So let’s actually run this here. And there we go. So now you see the original comments and comments. Okay, so other bits that are a little bit more useful, at least very similar to this style, is something that looks like this.

So we have select the same sort of thing that we have up here, but instead of a case statement, I can substitute the is null function. So it’s going to check to see if, if the comment is no then replace it with no comments. So both of these queries here are pretty much identical in operation and achieve the same results. But just remember that the is known as the built in function in SQL Server and not necessarily NC standard. So depending on the kind of database you are working with, or the kind of software you’re working with, that option may or may not exist. But let’s look at a few other options here. So another case statement, similar to one we have before, but we can continue to add more and more options to our case study, we don’t have to have just one or the other. This is also where the case statement is superior than the built in functions. Because the is no, it’s just one, one, right? We can only do one replacement, not multiple replacements. So here, we can actually have more than one option. So when comments is not an all then comments when internal comments is not an all then internal comments. Otherwise, we replace both instances of No, with no comments, that is equivalent to coalesce.

So it’s going to pick so the case statement is going to execute and, and well replace the value with whichever case becomes true first. So if the first win is true, then that’s not going to do the second when or the or the else. Likewise, if the second check when internal comments is true, then it’s not going to do the else clause, and so on and so forth. Right? Just like what if, if, if else if else else clause with do inside your programming languages. So we can do the same thing with the built in function called coalesce, which will, which will spit out or replace, whichever one is not null first. So if both comments and internal comments are no, then no comments will be actual placed in that column for that row. But if comments or internal comments is not null, then they will be used. So both of these two queries that I have up here on the screen are identical and functionality. And coalesce is a standard operation. So you would see that in a lot of other software databases as well. That is one of the other one of the main common patterns. Another common pattern that I see the case statement being useful, and common n is this situation here.

So slug person that E and PR is salesperson, and then for this particular column, and this is something that I haven’t highlighted yet, but notice that the case statement is a column, right? Not in the front, or where clause. So this is a projection, a projection operation. So case when salesperson is one, then sales department else not in sales. So a lot of times in databases, we’ll find ourselves to help storage space, or a lot of times our IDs and keys and things like that, that uniquely identify a row are numerical. And that doesn’t translate back well to our actual software that’s utilizing the database. So we can actually program in in our queries to replace those automatic numbers with the actual human readable text that that that that number replaces, or that number represents. So in this case, if the is salesperson is one, then they’re in sales, otherwise, they’re not in sales.

And you can see a couple of examples down here that show that replacement happening. This particular case statement is equivalent by the way to using an inline if statement. So, if if the salesperson is equal to one then sales department otherwise not in sales. So that is equivalent to the previous case statement that actually showed and do not write the if the inline f is not necessarily anti standard. So you may not see that and other database management systems that you may use. But that is the case statement and other useful functions that operate in a similar manner. And so remember the case statement that exists typically in your SELECT clause. And you can have multiple case statements in your select. So if you want to have a case statement for that gets applied to different columns, or represents other multiple columns that you want to project out. You can add multiple case statements in there if you so choose. And you can replace those some of those cases. Midsumma simpler ones with these other useful functions that I have listed here but remember coalesse is the only one here that is standard SQL

Variables

Video Transcription

Welcome back everyone. In this video, we’re going to take a look at variables and SQL. So variables are a little bit different than compared to your normal programming languages, primarily just the way they syntax that they’re, that they’re declared their scope and things like that. And we’ll be covering variables briefly in this video. And then we’ll cover them a little bit more in depth as we get to more some more complicated SQL examples. But to declare a variable, you actually use the declare statements. But you do not have to actually initially initialize a variable with a value, you can leave it blank, the default No, the default value is no if you do not include an as an initial value. So the syntax is always though included an at symbol, and the at symbol is going to make this name a variable Without it, your variable declaration will not work.

So declare some variable with the at sign, and int the data type it comes after the variable name. And then if you want to include initial value, you have to you can then do the assignment operator equals zero. And so since the assignment operator is being or the equals operator here is being used, and the declaration of a variable, not in a Boolean expression, it’s not it’s used as the assignment, not a Boolean comparison. So, again, there’s a whole bunch of different data types that we can utilize here, I’m only going to showcase a small number of them in my video here, and in the notes that I will link to documentation that will give you the long list of data types that are available to you to use inside SQL Server. So we can actually declare more than one variable as well, and one single statement. So you can’t do declare, XYZ, declare XYZ. But it’s more common, a little bit cleaner to do it in one single declare statement. So declare, and then each of those variables that you want to declare each one separated out by a comma. And this example here, I’m just using a integer int and a string type in bar car. And remember that the in is forcing that to be a Unicode string, and the in double quotes, and single quotes is an empty Unicode string.

Without the in next to the quotes, that string would just be a normal string. Not as critical here, when we’re talking about hard coded values. But when we talk about user input, it can be tricky to make sure that the input has been filtered out to only include Unicode characters, or UTF, eight or whichever encoding that the strings actually represent. But later in the class, when we start talking about integrating applications to utilize databases, we’ll talk a little bit more about filtering and things like that as far as from the UI to the database goes. But we can also convert some data types as well. And just like what you would cast with that, so just a little bit different different syntax here, as far as declaring variables go, data type goes into different place, then what you do in Java, and Python, right, you don’t have data type, so something to get used to here. But we can also convert data types, just like what you would expect to and most of your programming languages.

Casting is a standard SQL operation. So and very similar and other different languages as well. So if you wanted to cast a datatype, we can do cast and then the value that we’re trying to cast, and then as XYZ datatype, after it. So if we wanted to convert an int to a string, for example, this would be one possible way we could actually do it. The other way we can convert data types is the Convert operation convert function. This does give additional formatting options if you would like to utilize it. So convert, and then the type that we’re actually wanting to convert to, and then the value that we’re actually converting. So a little bit different syntax there. Although cast is mostly included in most standard SQL languages.

But let’s take a look at a few examples of this in action. So I just have a couple basic, a couple basic things up here. So this first statement, let me cut this out real quick. That is just my single variable, right and nothing actually comes out of it because it’s just a variable being declared. So I have some int value of type int. And I can select that, let’s see, just let’s just go and output that to our results and select if I want to do this in the query, and value and run this. So there is that column that value, the value of that variable that pops back out. And so I can utilize this anywhere, I would like to anywhere I would compare a single value inside of my query, I can use that variable there. But I can also convert that ends from an end to a string if I’d like, and a variety of other ways. But I can do the similar thing with converts. But convert is a little bit more useful in terms of especially things like date times. So if I wanted to remember what time it was, I can make a variable that stores the current date and time. And then I can convert that into a variety of formats. And if I wanted to, and so that converts feature is a little bit nicer in terms of working with dates, if you’re working with more the common data types, like INTZ, and strings and things like that.

The standard cast function works best there. So how do we utilize this inside of a query, so I can pull this code out here. So let’s declare a new variable. So salesperson ID 15. And so this is where variables start to shine a little bit more, because I can use a variable, very similar reason why I would use a variable and other things if I wanted to use a variable and more than the same value and more than one place, or just avoid hard coding, right. So if I use the salesperson ID, and multiple locations, this variable would become a little bit more important. So here is a local variable. And that we made up there. And let’s go ahead and give this a run. And now this is giving me all of the orders made by or fulfilled by salesperson ID 15. And this becomes a little bit easier to mess around with. If I wanted to change this to let’s say, salesperson five, and give us another run. And now I get all of the orders fulfilled by that particular salesperson. But there it looks like there is none for that. Let’s try there we go. So salesperson 14 had a few. The other salespeople I picked on didn’t quite have any. But this becomes a little bit easier to modify.

So my I don’t have to change my actual query itself, I can just change the value of the variable. And this becomes even more important, and we talked about stored procedures and functions in SQL later on down the road. But also note in some of my examples here, we will have statements like this, so go, and now I’m not going to cover go extensively in this lecture. Go is a batch operator in SQL. So it forces SQL SQL server here to consume all of the SQL SQL commands from the beginning or between go statements. So this executes all of this SQL code in one single batch. Because our variables exist only within the scope that they are actually defined. So if I have, for example, this so let’s go ahead and execute this code. Here. I have the results of my two queries. Here we are I have the Select up there this year, but I’m going to separate these two with go. And so each of these get executed in batch. So the first set of queries this gets executed first, and then the second batch gets executed next but Now, if I included this piece of SQL here right Ah, it no longer exists right the variable and the value is not found, because it only exists in this particular scope, right. So go can help restrict scope with variables, but go will be become a little bit more useful later down the road as we get to some more complex SQL lite transactions. So, now that we have taken a look at some variables and how to convert those variables, We’ll also explore some different data types along with that, but that will be for another video

Character Types

Video Transcription

Welcome back everyone. In this video, we’re going to be exploring a little bit more into different data types in SQL. So this first bit is going to be mostly on just text. So text comes in a variety of a couple different forms and databases, little bit more so in SQL Server, but we have a fixed length type, so char, and then in char, so in char being Unicode type, and we’ll talk about that here in just a minute. But this allows you to specify a column to be a fixed number of characters. So like, for example, a state is only two characters, long i k s, because this will help us reduce for one control the data that’s being inputted into our table, which will become a little bit more important as we talk about normalization and things like that. But it also helps with storage space as well, for anything that is, say you had a fixed character of size 10. But the value that you stored only had five characters, what the database system will actually do is pad the rest of that with spaces to consume that specific number of characters. But anything larger than that will not be able to be inserted.

But we can also have variable length data types. So our variable variable length strings, like var char and N var char. So in this sense, we can specify again, the number of characters that we expect, but nothing is actually padded. And so the storage is fit as not fixed anymore, right. So it adjusts to how many characters are actually in that given string. Now, this because this starts to showcase just a slightly different slight difference with how with how SQL Server manages strings versus other database management systems. So by default, now, if you are working with SQL Server 2019, or newer, which is what we’re working with, it supports UTF, eight encoded strings. But if you have anything that is not able to be encoded in UTF, in the specification, UTF eight, then you will have to use in char or n bar char.

So char and bar char versus in char and in bar char has a little bit more restriction as far as what what strings can be stored. So char and var char do not support all Unicode characters, and shower and Invar char will. So this becomes more important when you have user inputs. So if you have like say a form on a website, and not text then gets inputted into your database in some way. Your char and var char won’t necessarily be guaranteed to be able to consume and store those characters if they’re in different encoding. Specifically, if you have different languages and things like that different like English like English versus Spanish versus Italian versus Japanese and things like that, because Japanese characters are different Unicode values than English characters. But if you have a program and in between that is going to filter and filter and verify the the encoding of the text before it goes into your database.

Char and var char I would actually be the preferred data type to use because chart and bar char uses one byte per character to store verses in char and Invar char which uses two bytes per character to store. So char and var char are a little bit cheaper to store in your database versus an in char in bar char. So just like your programming languages, we have a variety of string functions that we can apply here. So both for ASCII and ASCII and characters, so char index substring, left and right I’ll show some examples of what this looks like here in just a few minutes. But substring char index, upper lower, very equivalent to most of the operations that you see in Python and Java. We can trim the whitespace off from the left side or the right side of the string. So left trim and right trim. Very similar to replace replicate and stuff. concat to bind strings together. So we’ll use concat. Quite, quite a lot. But let’s take a look at a few examples of these. And I’m not going to show examples of every single one of these functions. And again, I will link to some of the standard documentation for these. So you can explore and play with these a little bit more.

But let’s, let’s showcase a couple of these, to see them in action. Alright, so let’s take a look at this example here. So this is a big piece of this is a big piece of SQL, I have two SELECT statements here. This first one is equivalent or not equivalent to this first one is going to showcase concat. So concat is going to be equivalent to the plus operator, just like what you would expect in most. So if I wanted to add a comma to the country name, so country name, comma continent, and then this down here is the same thing. But using the concat. So concat, this with that, and that. So both of those are equivalent. So let’s highlight that and execute that code. And so you can see the country continent and country continent to being the exact same value. So the the string function can cat is equivalent to using the plus operator. But, but the difference here is that concat will actually replace null values with an empty value.

So that’s a little bit different functionality versus what you would expect with the plus operator. So concat would be the preferred way of joining strings inside of your SQL statements. We can also also wanted to showcase substring, which is another common one that you’ll be using. So we can showcase this so sub i can get parts of a string. So this is equivalent to the slice operation in Python, and the substring operation in Java. But we can pull out individual characters or range of characters from a given text or string. But again, I’m not going to take time to record a video to cover each and every single one of those, each and every single one of these string functions. Just because the video would be way too long and a little bit dry. So if you are interested in learning more about each of these, you can explore the documentation and I would encourage you to do so because some of these like upper lower, we might see and might use in a later exercise, homework or project

Date Time

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.