1
00:00:00,610 --> 00:00:01,840
Hello again, dear friends.

2
00:00:02,650 --> 00:00:08,230
So in this section, we will learn a date, date, time and time conversions.

3
00:00:09,310 --> 00:00:12,040
So why don't we start with the date conversion?

4
00:00:14,000 --> 00:00:19,010
Now, we might need to format a date in different formats per our requirements.

5
00:00:20,070 --> 00:00:27,120
We can use the Escorial convert function and Escorial server to format date time in various format.

6
00:00:28,610 --> 00:00:30,890
Now they convert function takes three parameters.

7
00:00:32,100 --> 00:00:33,810
The first parameter is data type.

8
00:00:34,730 --> 00:00:40,960
And we've learned those parameters of the convert function in the conversion function section before,

9
00:00:40,970 --> 00:00:44,780
so you don't quite remember them are little fuzzy when you go back and review.

10
00:00:45,200 --> 00:00:47,090
Otherwise, we can just carry on.

11
00:00:48,890 --> 00:00:50,240
I think you'll pick it up quickly.

12
00:00:51,680 --> 00:00:55,280
So the second parameter is the date that we want to convert.

13
00:00:56,680 --> 00:01:03,190
And the last parameter is the format code to convert a date in to the appropriate form.

14
00:01:06,400 --> 00:01:09,940
Now, here's a table that we can see various state formats in.

15
00:01:11,870 --> 00:01:15,500
And the date will be converted to VA char and the given format.

16
00:01:16,810 --> 00:01:24,850
Now, you can keep this table handy for referencing in the format of daytime columns.

17
00:01:26,220 --> 00:01:31,200
Now, the date formats are given in the result column, they're going to be different to each other.

18
00:01:32,300 --> 00:01:38,960
Because in some formats, the separating symbol was different and some formats, the order of the day

19
00:01:38,960 --> 00:01:39,710
part is different.

20
00:01:40,960 --> 00:01:45,850
So we've learned that the get date function returns the date and time of the machine.

21
00:01:46,890 --> 00:01:51,530
Also learned how to convert date to var char or string.

22
00:01:52,710 --> 00:01:57,630
So this query will convert the date to string in a default format.

23
00:01:59,940 --> 00:02:05,760
Now, we may need to convert the date into different formats for whatever our requirements will be,

24
00:02:06,120 --> 00:02:11,910
but at this point what we're going to do is we're going to use the third parameter of the convert function.

25
00:02:13,780 --> 00:02:22,000
So if we use no one as a third parameter, the date will convert into month date and the last two numbers

26
00:02:22,000 --> 00:02:25,240
of the year separated by a slash format.

27
00:02:27,620 --> 00:02:33,710
Now, let's convert all format in a single select statement to see the difference more clearly.

28
00:02:35,940 --> 00:02:43,410
So the name of the first format will be format one and the second will be format to just keep it clear.

29
00:02:49,340 --> 00:02:50,270
Format three.

30
00:02:53,710 --> 00:02:54,730
Format for.

31
00:02:59,120 --> 00:03:00,110
Format five.

32
00:03:04,120 --> 00:03:05,140
Format six.

33
00:03:07,890 --> 00:03:13,410
Format seven, and the last one is wait for it, format 10.

34
00:03:15,150 --> 00:03:16,680
So executed.

35
00:03:17,910 --> 00:03:22,650
And here it is, so all the formats have been listed as a table.

36
00:03:23,510 --> 00:03:31,820
Format one month, day and year separated by symbol format two, separated by dots format, three day,

37
00:03:31,820 --> 00:03:34,580
month and year separated by slash.

38
00:03:36,200 --> 00:03:37,460
And so on.

39
00:03:40,100 --> 00:03:42,410
So you can choose whichever format you need.

40
00:03:44,680 --> 00:03:52,090
So now let's retrieve a date from the Adventure Works 2017 database and convert it into different format.

41
00:03:54,370 --> 00:03:59,560
So we're going to use the employee table in the human resources schema.

42
00:04:02,140 --> 00:04:04,390
And here is the data of the table.

43
00:04:05,930 --> 00:04:13,160
And as you can see here, the birthday column, which we're going to use, I just want to let you know

44
00:04:13,160 --> 00:04:16,490
that it's not necessary to retrieve all the columns at this point.

45
00:04:16,500 --> 00:04:20,810
We'll just retrieve the birthday column, so we'll execute it.

46
00:04:22,070 --> 00:04:28,970
And here are the birthdays of all the employees magically listed, now we can also convert the birthday

47
00:04:28,970 --> 00:04:34,070
into different formats, so let's convert it into a format one.

48
00:04:35,630 --> 00:04:40,070
So we'll specify the third parameter as one and execute the query.

49
00:04:41,660 --> 00:04:43,310
And that's how you got to do.

50
00:04:44,240 --> 00:04:52,880
Now, in order to convert it into format 10, specify the third parameter as 10 and execute the query

51
00:04:53,480 --> 00:04:54,920
and that's it.

52
00:04:57,260 --> 00:05:01,520
All right, so now let's calculate the age of each employee.

53
00:05:02,550 --> 00:05:10,770
The date function returns a difference between the two dates, I believe that I remember quite clearly

54
00:05:10,770 --> 00:05:13,800
that we learned that date dysfunction in the previous section.

55
00:05:18,660 --> 00:05:26,220
And as you may recall, the date dysfunction takes three parameters, the first one will be year because

56
00:05:26,220 --> 00:05:27,670
we want to get the year.

57
00:05:27,840 --> 00:05:34,320
So the second parameter will be the birthday and the last parameter will be the current date.

58
00:05:35,490 --> 00:05:39,960
And that's all she wrote, so execute the query great.

59
00:05:41,010 --> 00:05:42,450
So here is a result.

60
00:05:43,340 --> 00:05:46,970
The age of each employee has been calculated successfully.

61
00:05:49,640 --> 00:05:53,890
And here are some of the other date formats, 11, 12 and so on.

62
00:05:56,510 --> 00:06:03,920
Now, the 23 one two one, one, two, one, two, three, and one of four format were turned whole

63
00:06:03,920 --> 00:06:05,290
numbers for the year.

64
00:06:06,140 --> 00:06:09,020
So this is where it differs from the other format.

65
00:06:10,790 --> 00:06:16,940
So on this table, the time formats are listed and these formats return only the time value from a date

66
00:06:16,940 --> 00:06:17,390
time.

67
00:06:18,500 --> 00:06:21,140
And you can choose any one of them for your requirement.

68
00:06:22,330 --> 00:06:27,940
So here's the query for each format, so why don't we just execute it and get the result?

69
00:06:30,590 --> 00:06:32,030
And here we can see.

70
00:06:33,100 --> 00:06:37,300
Now, there are different formats of time have been listed in this table.

71
00:06:39,160 --> 00:06:45,010
And the time formats will return only the time value from the date time.

72
00:06:46,500 --> 00:06:48,570
So what about date and time format's?

73
00:06:50,570 --> 00:06:54,380
The format in this table returned both date and time.

74
00:06:55,820 --> 00:06:59,570
There are different date time and separating symbol combinations.

75
00:07:00,870 --> 00:07:06,900
So here are all the date and time formats here in a single select statement.

76
00:07:08,630 --> 00:07:11,210
So let's go ahead and execute it and see the result.

77
00:07:11,900 --> 00:07:17,780
So all the different date and time formats of the date have been listed and of course, you can use

78
00:07:17,780 --> 00:07:19,250
any one of them in your project.

79
00:07:19,290 --> 00:07:21,160
It just depends on your requirements.

80
00:07:22,340 --> 00:07:29,330
And that's that, so in this section, we have learned the date and time format.

81
00:07:30,370 --> 00:07:37,000
Now, in the following section, we're going to learn string functions, so hold on to your hats, ladies

82
00:07:37,000 --> 00:07:39,250
and gentlemen, because I want to see you there.