Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome back the Model four sequel programming to currently in Lesson one sequel functions, and we're moving into sub lesson 1.3 functions continued with date functions.
00:10
So we're going to start off with Thea Table in the data base that we used in the previous lesson. And that's the employees table in the employees database that we had established in an earlier lesson.
00:23
So I have
00:24
the select statement that pulls out all the data. We're gonna go ahead and upended new data to it.
00:30
And if we recall, we know that a function is a
00:34
a piece of code that does something very specific
00:38
now the included functions in my sequel. The code is actually out of our view. We don't view it. We just need to know the functions, name
00:48
and what that function does for us.
00:50
So in this lesson, we're gonna take a look at date functions, and we're gonna see how how we might use the date function.
00:57
Two different transform
00:59
some of this date data, and it's something that might be useful
01:03
in a way of looking at it from a different perspective.
01:07
For example, we have the higher date which is a date, but maybe we want to know how many days this person has been hired. I can see that somebody was hired in 1985
01:18
but I have no idea how many days that equates to. And maybe there's some reason I want to know that
01:23
now, before we get started in this lesson, something that might help is if you go to the employees table by just clicking on it,
01:29
go ahead and change one of the higher dates. Maybe this George Percy Low because he seems to end up at the top,
01:36
um, to a date that's five days before your current date or 10 days. However, number days seems convenient to you so that you can verify that it's working, because when you try to ah,
01:47
in your head, calculate the number of days since 1985 11 21 it's not as easy as a date. That's five or 10 days ago,
01:55
so I didn't switch one of those you can. You can simply click into the Cell and D Beaver, edit the data and then click the save button. It's that easy.
02:05
Let's go back to the query
02:07
that we were working with on Let's add in a date function.
02:10
So
02:13
my cycle has a function called date death,
02:15
which is the difference between two dates
02:19
and the order of operations matters here. So if you subtract a larger date from a a smaller date, then of course you're gonna get a negative number, which may not make sense.
02:30
So we need to start off with the current date. We want the number of days since this individual was hired,
02:37
so the larger date is gonna be the current date. So how do we get the current date? Well, we use another function called now
02:45
and now does exactly what you're thinking. It does. It gives us back a date. That is now, and now is whatever time it is when you run this query.
02:57
So we need a passing our other argument,
02:59
which is our
03:00
higher date,
03:05
and then we're gonna call this as days
03:08
hired.
03:10
So we've called the date date function. We've passed in the now function, which returns
03:16
the current dates
03:19
at the time of run. And then we fought for the, um,
03:23
for the other argument we've passed in the higher date.
03:25
So this is going to essentially equate to
03:29
now
03:30
minus higher date.
03:32
Now, we had reversed that,
03:36
and we had put hired eight minus. Now
03:39
we would have ended up with a native number because the higher dates a smaller number.
03:46
Let's get rid of that. Let's go ahead and run this and make sure we get what we expect.
03:55
Okay? So you could see the date that I changed to be five days prior to my current date. Correctly yields be five.
04:02
And I'm pretty confident these air correct, too.
04:08
So it's been a lot of days that a lot of these people have been hired,
04:12
which would make me wonder if this application is still being used. If I just happened to stumble upon a unused database,
04:19
Okay. So let's, uh, take another look at this.
04:24
We could get somebody's age out of here to Currently we have the birthdate. Maybe we want to know how old these people are. I see their birthday, but I'd like to know just how old they are
04:36
on, Uh, how would we do that again? Again? We're gonna use the date def function.
04:45
We're going again, passing
04:46
now
04:48
and again. We're gonna pass in the field of interest, which is the birth date,
04:54
and we're saying as
04:57
years old,
05:00
let's go ahead and run this and make sure we get E uh, what we expect to see and I expect to see days, not years. But we'll fix that in a minute.
05:09
Okay,
05:11
so this is currently the days that they are old. It's not the years old, but we want the years old. That's commonly what we discussed. We're talking about an individual's age,
05:20
So how would we do that here? Well, there's 365 days
05:28
in a year.
05:29
So
05:30
if I divide this number by 3 65 I should get the number of years.
05:35
I should be able to convert this number two the number of years. Let's go ahead and give that a try. So we're just don't put
05:41
divide by 3 65
05:45
Let's run it again and see what we get.
05:46
Okay, we're getting We're definitely get a lot closer, but we're getting decimal numbers
05:51
so we can see that this guy is essentially 58 a half years old.
05:56
Let's say we just want the age
05:58
now. Do we want to round up around down. So if someone's not quite 55 yet, do we want to say, Hey, we're gonna round you up to 55 or we're gonna run you down to 54.
06:08
Ah, common way that we look at that is that hate? You're not that age until you pass that birthday, so this guy will be 54. So how are we gonna do that? We need to use another function that's called the floor function. And what the floor function does is it
06:25
takes a decimal number and rounds it down, regardless of what the decimal is. So 54.999 will become 54.
06:34
So let's go ahead.
06:38
And there isn't the opposite of this, which is ceiling, which does the exact opposite rounds it up regardless of what it iss. So let's go ahead and floor of this, though. So the way this is working is we are getting
06:49
the date difference between now and the birthday. We're defied ing that number by 365 and then when this resolves, we're passing it into the floor function and the floor function will simply round every decimal number down to an imager so that's going to run this.
07:11
And sure enough, that's exactly what happened.
07:14
We got 65 54 59 64 64 so we can see these individuals ages.
07:20
I moved on that using date functions. So that completes this lesson. And to recap we touched further on the utility of functions and what functions are again functions are just pieces of code that do something specific. Date def
07:35
is a function. It does something specific. That specific thing that it does is it gets the difference between dates.
07:42
We use the day def with the higher date to find out how many days they've been hired for.
07:46
We then use the date de function with the birthday field to find out how many how many days old they are. We then took that answer. I divide it by 3 65 to find out how many years old they were. And finally, because this produced a decimal number,
08:05
we floored that decimal number with the floor function which made it so that it simply rounded it down to the nearest manager, which is a whole number.
08:16
And that completes this lesson. I hope you got your date functions working, and I hope to see you in the next lesson. Thank you. Bye.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor