1
00:00:00,000 --> 00:00:05,870
(Music)

2
00:00:05,870 --> 00:00:09,690
Hello and welcome. In this video,

3
00:00:09,690 --> 00:00:12,300
we'll go over date and time SQL functions built into

4
00:00:12,300 --> 00:00:16,470
the database. So let's get started.

5
00:00:16,470 --> 00:00:19,800
Most databases contain special data types

6
00:00:19,800 --> 00:00:21,705
for dates and times.

7
00:00:21,705 --> 00:00:27,040
Db2 contains date, time, and timestamp types.

8
00:00:27,040 --> 00:00:30,035
In Db2, date has eight digits:

9
00:00:30,035 --> 00:00:32,825
for year, month, and day.

10
00:00:32,825 --> 00:00:34,240
Time has six digits:

11
00:00:34,240 --> 00:00:36,510
hours, minutes, and seconds.

12
00:00:36,510 --> 00:00:39,740
Timestamp has 20 digits: year,

13
00:00:39,740 --> 00:00:43,814
month, day, hour, minute, seconds,

14
00:00:43,814 --> 00:00:47,530
and microseconds where double X represents month

15
00:00:47,530 --> 00:00:52,520
and six Zs or Zs represents microseconds.

16
00:00:52,520 --> 00:00:55,095
Functions exist to extract the day,

17
00:00:55,095 --> 00:00:56,150
month, day of month,

18
00:00:56,150 --> 00:00:58,775
day of week, day of year,

19
00:00:58,775 --> 00:01:02,405
week, hour, minute, and second.

20
00:01:02,405 --> 00:01:04,630
Let us look at some examples of

21
00:01:04,630 --> 00:01:06,405
queries for date and time functions.

22
00:01:06,405 --> 00:01:08,610
The day function can be used to

23
00:01:08,610 --> 00:01:11,280
extract the day portion from a date.

24
00:01:11,280 --> 00:01:12,980
For example, to get the day portion

25
00:01:12,980 --> 00:01:16,665
for each rescue date involving cat,

26
00:01:16,665 --> 00:01:16,915
select

27
00:01:16,915 --> 00:01:28,420
DAY (RESCUEDATE) from PETRESCUE where ANIMAL equals cat.

28
00:01:28,420 --> 00:01:32,025
Date and time functions can be used in the where clause.

29
00:01:32,025 --> 00:01:34,080
For example, to get the number

30
00:01:34,080 --> 00:01:36,465
of rescues during the month of May,

31
00:01:36,465 --> 00:01:38,250
that is, from Month 5,

32
00:01:38,250 --> 00:01:40,940
select COUNT star from PETRESCUE

33
00:01:40,940 --> 00:01:48,815
where MONTH (RESCUEDATE) equals 05.

34
00:01:48,815 --> 00:01:52,345
You can also perform date or time arithmetic.

35
00:01:52,345 --> 00:01:54,780
For example, to find out what

36
00:01:54,780 --> 00:01:56,455
date it is three days

37
00:01:56,455 --> 00:01:57,270
after each rescue date,

38
00:01:57,270 --> 00:01:58,610
maybe you

39
00:01:58,610 --> 00:01:59,260
want to know this

40
00:01:59,260 --> 00:02:00,535
because the rescue needs to be

41
00:02:00,535 --> 00:02:02,025
processed within three days.

42
00:02:02,025 --> 00:02:11,975
select (RESCUEDATE plus three DAYS) from PETRESCUE.

43
00:02:11,975 --> 00:02:14,970
Special registers current time

44
00:02:14,970 --> 00:02:16,640
and current date are also available.

45
00:02:16,640 --> 00:02:19,400
For example, to find how many days

46
00:02:19,400 --> 00:02:22,145
have passed since each rescue date till now-

47
00:02:22,485 --> 00:02:28,740
(CURRENT_DATE minus RESCUEDATE) from PETRESCUE.

48
00:02:28,740 --> 00:02:31,715
The result will be in years, months, days.

49
00:02:31,715 --> 00:02:34,400
In this video, we looked at different types of

50
00:02:34,400 --> 00:02:36,760
built in SQL functions for working

51
00:02:36,760 --> 00:02:38,020
with dates and times.

52
00:02:38,020 --> 00:02:40,250
Thanks for watching.

53
00:02:40,250 --> 00:02:45,000
(Music)