1
00:00:00,090 --> 00:00:02,730
So far we've seen a lot with comparing numbers.

2
00:00:02,730 --> 00:00:07,620
Let's talk briefly about comparing dates and times and date times and time stamps.

3
00:00:08,070 --> 00:00:14,190
We can use the operators we've already seen like less than or greater than greater than or equal to

4
00:00:14,550 --> 00:00:16,020
to make comparisons.

5
00:00:16,020 --> 00:00:17,880
Let's work with our people table.

6
00:00:17,880 --> 00:00:20,850
So let's do a select start from people from earlier.

7
00:00:20,850 --> 00:00:28,920
When I covered dates and date times and times, let's say that I want to select the people who were

8
00:00:28,920 --> 00:00:33,000
born before the year 1999.

9
00:00:33,000 --> 00:00:35,130
Or how about before 2005?

10
00:00:35,130 --> 00:00:35,880
Let's do that.

11
00:00:36,690 --> 00:00:43,830
So to do that, I would do a select star or whatever I want to select from people where, and then I

12
00:00:43,830 --> 00:00:48,060
can take the birth date column, which is a date, right?

13
00:00:48,060 --> 00:00:52,890
It's going to be in this format of 2000 and something or whatever the date is.

14
00:00:53,400 --> 00:00:58,290
And I'm going to compare that to where it's less than 2005.

15
00:00:59,040 --> 00:01:02,330
And then what, January 1st of 2005.

16
00:01:02,340 --> 00:01:04,560
And this is a string, right?

17
00:01:04,560 --> 00:01:06,870
This is not a date, but it's a string.

18
00:01:06,870 --> 00:01:13,920
And most of the time, Miko can figure out how to compare an actual date to a string that looks like

19
00:01:13,920 --> 00:01:14,460
a date.

20
00:01:14,460 --> 00:01:16,110
And it does work.

21
00:01:16,110 --> 00:01:22,860
We get the two people, Elton and Lulu, who were born before 2005, January 1st, right.

22
00:01:22,860 --> 00:01:24,750
So 2019 85.

23
00:01:25,320 --> 00:01:26,280
So that does work.

24
00:01:26,280 --> 00:01:36,510
But another option that is more foolproof is to first extract the year from birth date, if that's all

25
00:01:36,510 --> 00:01:42,990
I care about, right, year from birth date and say was the year before 2005.

26
00:01:43,570 --> 00:01:45,310
And we get the same two results.

27
00:01:45,310 --> 00:01:49,540
But this is a lot more explicit and it has fewer places.

28
00:01:49,540 --> 00:01:50,640
It could go wrong.

29
00:01:50,650 --> 00:01:55,840
This relies on my SQL recognizing this as a date and it being in the correct format.

30
00:01:55,840 --> 00:01:59,770
And if I only care about the year, then it's better to extract the year.

31
00:02:00,250 --> 00:02:04,330
Again, this will work most of the time, but it's not always guaranteed to work.

32
00:02:04,330 --> 00:02:09,850
And in fact, the docs make a note about just this very topic, which I'll show you in a moment.

33
00:02:10,240 --> 00:02:11,950
We could also work with times, right?

34
00:02:11,950 --> 00:02:21,130
If I wanted to know if somebody was born after nine in the morning, I could do this.

35
00:02:21,130 --> 00:02:27,970
So star from people where I think we call it birth time is greater than.

36
00:02:28,950 --> 00:02:32,520
And then I would need to have my date string in the correct format.

37
00:02:32,520 --> 00:02:37,140
So something like 090000 that would work.

38
00:02:37,140 --> 00:02:39,450
And ah, all these people born after nine in the morning.

39
00:02:39,450 --> 00:02:40,200
I guess so.

40
00:02:40,200 --> 00:02:42,400
So let's change it to narrow it down a bit.

41
00:02:42,420 --> 00:02:45,960
How about people born after noon, after 12?

42
00:02:46,140 --> 00:02:51,180
So these two people, Juan and Hazel, that does work.

43
00:02:51,180 --> 00:02:55,620
But again, we're comparing a true time to a string.

44
00:02:55,620 --> 00:03:02,220
So if I just want to know if they were born after 12:00, I could do a similar approach where I say

45
00:03:02,400 --> 00:03:05,460
the our portion of birth time.

46
00:03:05,460 --> 00:03:08,910
Remember, we saw all these time functions and date functions.

47
00:03:09,300 --> 00:03:10,710
That's going to give me a number.

48
00:03:10,710 --> 00:03:16,470
It's going to extract the hour and that I can compare that to nine or 12 or whatever we said and we

49
00:03:16,470 --> 00:03:17,430
get the same two.

50
00:03:18,150 --> 00:03:21,210
And there is one more option that I'm going to show you.

51
00:03:21,210 --> 00:03:25,410
There is a function built in to my SQL called caste.

52
00:03:25,800 --> 00:03:35,280
Caste helps us convert one data type to another so I could cast a string into an actual date, for example,

53
00:03:35,280 --> 00:03:41,520
or caste a date time into a date and things like that.

54
00:03:41,820 --> 00:03:47,310
So the format is that we cast some value as some type.

55
00:03:47,310 --> 00:03:49,290
So I would do something like this.

56
00:03:49,290 --> 00:03:51,060
Select cast.

57
00:03:51,090 --> 00:03:55,380
Let's take the string 090000.

58
00:03:56,920 --> 00:03:58,390
Now I hit enter too soon.

59
00:03:58,420 --> 00:03:59,710
All right, let's try that again.

60
00:04:00,070 --> 00:04:01,390
Let's take the string.

61
00:04:01,420 --> 00:04:04,240
090000.

62
00:04:04,690 --> 00:04:07,720
And cast that as a time.

63
00:04:07,720 --> 00:04:09,460
Because this is a piece of text.

64
00:04:09,460 --> 00:04:11,260
It is not truly a time.

65
00:04:11,350 --> 00:04:14,710
It could just as well be Hello world in those quotes.

66
00:04:14,710 --> 00:04:16,420
But here we're telling my SQL.

67
00:04:16,420 --> 00:04:20,980
We want it to be turned into a time and it's not going to look any different when it's printed out,

68
00:04:20,980 --> 00:04:25,540
although I suppose it might look different if I did something like this, right.

69
00:04:25,600 --> 00:04:27,070
I don't have that leading zero.

70
00:04:27,070 --> 00:04:31,600
Well, it can figure out that we should have it and it formats it into the correct time.

71
00:04:31,990 --> 00:04:37,570
I know it's confusing because it looks pretty much the same, but as far as my SQL knows, this is a

72
00:04:37,570 --> 00:04:38,440
piece of text.

73
00:04:38,620 --> 00:04:40,600
Now we've told it, it's a time.

74
00:04:40,600 --> 00:04:43,660
And so what we get back from this cast is a time.

75
00:04:43,660 --> 00:04:47,860
So the reason I bring this up is we can use this when we're making comparisons.

76
00:04:48,340 --> 00:04:52,690
The docs recommend that you do this when you're working with between.

77
00:04:52,690 --> 00:04:58,360
If I wanted to find people who are born in the afternoon, so people who are born between, let's just

78
00:04:58,360 --> 00:05:06,760
do select star from people between 12:00 and I don't know, 4:00 or if we use 24 hour time, what is

79
00:05:06,760 --> 00:05:08,560
that from 12 to 16?

80
00:05:08,560 --> 00:05:15,940
So select star from people and I could do where and then birth time between.

81
00:05:16,630 --> 00:05:20,220
And then I have my first hour would be 12:00.

82
00:05:21,370 --> 00:05:27,340
And and then 16:00 like that and it works.

83
00:05:27,340 --> 00:05:31,300
We find the one person, Hazel, who was born at 14 5829.

84
00:05:31,510 --> 00:05:32,650
So this does work.

85
00:05:32,650 --> 00:05:37,270
It's one of those things where it's almost always going to work, thanks to my SQL, figuring out what

86
00:05:37,270 --> 00:05:37,930
we want.

87
00:05:37,930 --> 00:05:44,470
But the technically correct way of doing this is to turn these strings into times, and we can do that

88
00:05:44,470 --> 00:05:47,230
using cast so we can cast.

89
00:05:47,230 --> 00:05:52,750
And I think I'm actually going to do this in my editor just so you can see it all and not have it wrapped

90
00:05:52,750 --> 00:05:55,150
around on a line and getting cut off.

91
00:05:55,750 --> 00:05:59,170
So we do cast this time.

92
00:06:00,020 --> 00:06:02,150
As an actual time, right?

93
00:06:02,150 --> 00:06:05,870
Because this is not a time data type, it's text, it's a string.

94
00:06:06,260 --> 00:06:10,910
And then we'll say and cast what was our other time?

95
00:06:10,910 --> 00:06:14,060
16:00 I think as a time.

96
00:06:14,570 --> 00:06:19,190
Now this is going to seem like a lot of work for something that already was working with fewer characters.

97
00:06:19,190 --> 00:06:20,300
And that is true.

98
00:06:20,810 --> 00:06:23,810
I'm just being thorough here, but we'll get the same result.

99
00:06:23,990 --> 00:06:30,980
And if all we actually cared about was the hours portion, then what I think is easiest is to just extract

100
00:06:30,980 --> 00:06:34,310
the hour portion from birth time and then compare that to a number.

101
00:06:34,400 --> 00:06:37,030
I don't have to write these long date or time strings.

102
00:06:37,040 --> 00:06:38,600
I could just do what we did earlier.

103
00:06:38,600 --> 00:06:40,100
Select Star from people.

104
00:06:40,940 --> 00:06:48,320
Where birth time is between and instead of all of birth time, I want the hour portion of birth time

105
00:06:48,320 --> 00:06:49,430
that gives me a number.

106
00:06:49,640 --> 00:06:50,510
And where is that?

107
00:06:50,510 --> 00:06:53,660
Between 12 and 16.

108
00:06:54,200 --> 00:06:55,940
So I think this is simpler.

109
00:06:56,360 --> 00:06:57,650
But if you needed to have

110
00:06:59,120 --> 00:07:09,800
1231 and 30 seconds, then this syntax would work great because you can be way more precise with your

111
00:07:09,800 --> 00:07:11,230
times and minutes and seconds.

112
00:07:11,240 --> 00:07:13,350
It's not just limited to one aspect.

113
00:07:13,370 --> 00:07:18,200
But anyway, let's run this and just make sure that works too, and we get the same result.

114
00:07:18,380 --> 00:07:22,040
So there's a lot of comparisons we can do with dates, times, date times, time stamps.

115
00:07:22,040 --> 00:07:24,290
I didn't even show date times and time stamps.

116
00:07:24,290 --> 00:07:29,200
But in general, what I always recommend is make the simplest comparison that you can.

117
00:07:29,210 --> 00:07:34,010
If you do, you need to care about seconds and minutes or even if you have a date time.

118
00:07:34,010 --> 00:07:37,190
Do you care about the time portion or just the date portion?

119
00:07:37,220 --> 00:07:44,210
If I wanted to know if somebody was born before this date, well, this is a date time, but I could

120
00:07:44,210 --> 00:07:46,220
just compare to the date portion.

121
00:07:46,220 --> 00:07:50,630
I don't really care about the time unless I need something that detailed.

122
00:07:50,630 --> 00:07:52,910
I need to know about the time as well.

123
00:07:53,150 --> 00:07:55,400
So we can cast as we saw.

124
00:07:55,760 --> 00:08:01,460
You're not going to notice a difference most of the time because my SQL can figure out that when we

125
00:08:01,460 --> 00:08:07,850
don't cast this to a time, it can tell that this conforms to the time syntax, the correct format and

126
00:08:07,850 --> 00:08:10,520
birth time is a time and it compares them for us.

127
00:08:10,520 --> 00:08:15,020
But this is the technically correct version and it's what the docs recommend you do.