Hello again, dear friends.
So in this section, we will learn a date, date, time and time conversions.
So why don't we start with the date conversion?
Now, we might need to format a date in different formats per our requirements.
We can use the Escorial convert function and Escorial server to format date time in various format.
Now they convert function takes three parameters.
The first parameter is data type.
And we've learned those parameters of the convert function in the conversion function section before,
so you don't quite remember them are little fuzzy when you go back and review.
Otherwise, we can just carry on.
I think you'll pick it up quickly.
So the second parameter is the date that we want to convert.
And the last parameter is the format code to convert a date in to the appropriate form.
Now, here's a table that we can see various state formats in.
And the date will be converted to VA char and the given format.
Now, you can keep this table handy for referencing in the format of daytime columns.
Now, the date formats are given in the result column, they're going to be different to each other.
Because in some formats, the separating symbol was different and some formats, the order of the day
part is different.
So we've learned that the get date function returns the date and time of the machine.
Also learned how to convert date to var char or string.
So this query will convert the date to string in a default format.
Now, we may need to convert the date into different formats for whatever our requirements will be,
but at this point what we're going to do is we're going to use the third parameter of the convert function.
So if we use no one as a third parameter, the date will convert into month date and the last two numbers
of the year separated by a slash format.
Now, let's convert all format in a single select statement to see the difference more clearly.
So the name of the first format will be format one and the second will be format to just keep it clear.
Format three.
Format for.
Format five.
Format six.
Format seven, and the last one is wait for it, format 10.
So executed.
And here it is, so all the formats have been listed as a table.
Format one month, day and year separated by symbol format two, separated by dots format, three day,
month and year separated by slash.
And so on.
So you can choose whichever format you need.
So now let's retrieve a date from the Adventure Works 2017 database and convert it into different format.
So we're going to use the employee table in the human resources schema.
And here is the data of the table.
And as you can see here, the birthday column, which we're going to use, I just want to let you know
that it's not necessary to retrieve all the columns at this point.
We'll just retrieve the birthday column, so we'll execute it.
And here are the birthdays of all the employees magically listed, now we can also convert the birthday
into different formats, so let's convert it into a format one.
So we'll specify the third parameter as one and execute the query.
And that's how you got to do.
Now, in order to convert it into format 10, specify the third parameter as 10 and execute the query
and that's it.
All right, so now let's calculate the age of each employee.
The date function returns a difference between the two dates, I believe that I remember quite clearly
that we learned that date dysfunction in the previous section.
And as you may recall, the date dysfunction takes three parameters, the first one will be year because
we want to get the year.
So the second parameter will be the birthday and the last parameter will be the current date.
And that's all she wrote, so execute the query great.
So here is a result.
The age of each employee has been calculated successfully.
And here are some of the other date formats, 11, 12 and so on.
Now, the 23 one two one, one, two, one, two, three, and one of four format were turned whole
numbers for the year.
So this is where it differs from the other format.
So on this table, the time formats are listed and these formats return only the time value from a date
time.
And you can choose any one of them for your requirement.
So here's the query for each format, so why don't we just execute it and get the result?
And here we can see.
Now, there are different formats of time have been listed in this table.
And the time formats will return only the time value from the date time.
So what about date and time format's?
The format in this table returned both date and time.
There are different date time and separating symbol combinations.
So here are all the date and time formats here in a single select statement.
So let's go ahead and execute it and see the result.
So all the different date and time formats of the date have been listed and of course, you can use
any one of them in your project.
It just depends on your requirements.
And that's that, so in this section, we have learned the date and time format.
Now, in the following section, we're going to learn string functions, so hold on to your hats, ladies
and gentlemen, because I want to see you there.