Working with SQL Server Dates and Times | Arindam Mondal | Skillshare

Working with SQL Server Dates and Times

Arindam Mondal

Working with SQL Server Dates and Times

Arindam Mondal

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
5 Lessons (24m)
    • 1. Introduction

      1:21
    • 2. DataTypes

      8:24
    • 3. Functions

      10:54
    • 4. Converting Dates

      3:16
    • 5. Conclusion

      0:12
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

1

Student

--

Projects

About This Class

In this class I will discuss about Date and Time data types in SQL Server.

Are you working with SQL Server database? Then you should have good knowledge on date and time data types. These data types are one of the most used data types in a table. I will also cover date functions and date conversion.

Let's start, I will see you in the class.

Meet Your Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: Hello and welcome to the course. Whacking on single server dates and times. I am only nominal. I have over 10 years of experience in IT focusing on databases and Cloud technologies. If you are new to the SQL server, then this course will be very helpful for you to understand how dates and times working SQL Server. And if you have already worked on SQL Server, then you can refresh your knowledge on dates and time series. In this course, we will mainly focus on three major areas, date and time datatypes. Different date and time data types. We'll talk about those database and how you can effectively use those datatypes in SQL tables. And next date and time functions. Sql Server provides date and time functions so that you can use different functions in your query as well. The applications need. And lastly, we'll discuss dead convulsion. Here we will discuss how to convert 1-bit day-to-day two MMF DB today. So let's begin. See you in St. backlit. 2. DataTypes: Let's talk about big data types in SQL Server. Sql seven or six different date and time data types. These are time. Did smartly team did did tend to and let them sit. Let's discuss time little tape. It use to store only time part, which is based on 24 hour clock. The default string format is 80, mmm. And it says with fractional second part, which is up to 70. And the default value is 0 for all our mini ten seconds. Now Alice two digits ranging from 0 to 23. That represents the hour. The minute parties two digits ranging from 0 to 59, that it represents the meaning. And the second is also two digits ranging from 0 to 59, that will represent the second. The next is did the data if it is used to store it. The string format is way, way, way, way in m and b, b. And the default value is 1900 for year, 014 month, and 001 for B. The next data type is small leptin. Use to store data elements time. But let's say always stored as 0 and fractional seconds, not every level eat small LED template attain. It uses 24 hour time for my next one is named intuitive. It used to store data like time value. And each store as 24 hour format. That the Falwell for date portion is 9800, 0, 1, 0, 1, and 4 time Park. All Alphasat 0 that they tend to hold it at I is an extension merchant of debt payment today, which has larger date range and Fractional of that default value is same as the Temple Mount in 100, 0101. And all zeros for time part that IT team of subdata type stores the time with time genuineness. Timelines. Is this done 24 hour clock? Let's see an example of time day-to-day. Here we're creating a table, column one with diameter Dave, Colombia with time fractional 0. As we have not specified any fractional part with column one time later time. So it would take seven by default. As we have 0 for XML for column two. So it should not take any fractional second part. Here I am inserting two rules. The first one is with some time in hour minute second format in the second row. Inserting it and get DID value, which provides the current system that then let's execute the statements together. You can see column one is for XML, Second World War up to seven. And Colombia has no flexion and as we have 0, the time needed to type. Now let's see how dead datatype works. It is used to store data in year, month and date format. Here I am creating a development the date data type, and inserting two rows of Astronomy, get dead venues. And secondarily hard-coded date and time may lose. Let's execute the statements together. You can see in the result that time STEMI is ignored. Normally the date value stored. Let's see an example of small leptin. It used to store data and time together as year, month, day, and then hour, minutes, seconds. When the second oldest source 0. Let's take this and getting a double layer. With small let Tim column. And inserting two rows, we get dead values and some art could edit Tim Deleuze. Let's execute the SQL statements together. Can see here that isn't the second party string 0. So we still use depend data type instead of small dip. Then if we want to store the second part, we'll discuss it next. Now let's talk about the 10 data type. It's stored. Dare to Lead Time Value. That time parties based on 24 hour clock. Let's see the example. So I'm using this table. Column one has the same data type. Using git diff function to insert some filled at 10 mil. Let's execute it all together. This is the result that I'm portion is less than 24 hour clock. And you can see the effects. And I'll second part is up to three values. Now let's talk about the attempt to data type. It is similar to the template. It has larger date range and larger fractional precision. So here I'm creating a table, column one as they tend to datatype and inserting one rules with good data values. Let's execute it. You can see there is a nephew compare it to the database. You attempt to as mode fractional. Second part. Now let's see an example of dead time of set data type. It is still stored and mid-tempo with time Jonelle harness. The timeline is based on 24 hour clock and the time going off-script. Then this minus 14 2 plus 14 times monopsony domains. But Joan offset value from UBC for a given at 10 million. Let's create the table with column one and they Tim offset. And I'm inserting there get DID value. And let's see that ism can see there is an EPS sharing that data with time Joan offset as 0. I have created some coding exercise for this class, which you will find in project section below. Do not forget to do the coding exercise. It will help you to understand this plasmid. If you have any sense, you can post it below. 3. Functions: In this video, we will discuss various date functions available in SQL Server. The first system, which returns, they tend to will that contents that date and time on the sandbox where the SQL instance Islam in the next function is current timestamp. It also be that the system timestamp in depth ten pill that get their function. Also the TMS system timestamp from where the sequence server instance is running. Let's take all these three functions. You can see that since that time has done more for XML, second part, and current timestamp and get knit both returns similar valves. The next function is d. It returns they promised to spread date value. If that did contents, only a tiny part, they need to return one as the base state. The month function of the dumps. The month very specific DID and the year 570 temps the year value premise was fed did you did contents only a tiny part then it times 1900, which is the BCL. Let's see example. I'm when I'm in deep function for good data in the state. So both returning that day will come that a pin. Now checking the month function is returning the month value integer day 10. Let's take the f function. It is returning year value from the display date time. Let's see other functions. That data function. This function at day 10 below to a date based on the interval provided and then returns the result. That didn't lm function returns part of debt based on the interval prevented. The return value is instinct format that it put function returns, portion offered date based on the interval provided. And let me turn my Louise in integer format. The density function returns difference between two dates. Based on the internal provided. There is dysfunction. Takes any input and check firm hellish deaths by Yeoman function takes data as input. Emily damps the last day of month. Next function is dead end function. This function add or subtract a number to a stress fat, the fat, and the times that I have written this query to understand the type function. This function takes three inputs, a very fast cronies. Format. Here we can say Depart format. Like the hour, minutes, seconds. Here a while. A stands for ear part. And the second input is the number integer value. It can be any positive number or negative number. So for positive number it will end up with the Swiss where did part. And for negative number it will subtract from the Fed did for. The third input is date value. So these data function will add one year from the provided the Advil. Similarly, I use different data in the data function. Quite be off here, D here, weekday, week, day, hour, minute. And second. Let's execute the query. Can see here the given data. And that did 14 load data function. And that is else. Let's discuss the CNAME function. The function returns, but often missed on date. Reminded, the return value is in string form. Here I am using getName function for the data and using it did richly dense current system. They I mean the different islands and I'm also using some mumbled it. But when you lay Quartal month, year BY week, WE Day and our let's execute all statements together. Can see here that the 10 mil mean the second column, that date format. And there is some value. In 2021. What our value is one month is March. Be off here is 79. Days, 20 Week 12, weekdays, Saturday. And our already saved pin. Now let's talk about did PR function. It returns it as an integer value. Let's see the query. Here. I am using videos, did part parliament to get this this way. And Huawei for you for quarter 4 D up the year. Before, did part B. Wk for week, dw for weekday. For our MI for minute is for second. Ms. Four milliseconds. I get debt for all Bedford function. Let's execute it. You can see the result here, the update far results and the timestamp. And the faster we have dipping year, quarter, month. Next day off here, next day, which is 20. The next one is weak. And then be off week. Next hour, which is eight pin. Next on his mini 2, which is 16. And the obtained through is second is 25. And the last 20s millisecond, which is 493. Now let's talk about the function. It returns the differences between two dates in the Swiss fed interval. For me. Here in the function where we've seen to date, this is the first dip. This is the second date. And the interval format this year. So I have to spread other interval Melos, slake, quarter, month layoff here. Be weak. We need second milliseconds and microseconds. Let's say it's good it together. You can see the result here. Year differences, one, quarter differences for month differences. Day off here differences 365 day differences, 365 week difference, POP3. And there is the RD Prince minute difference, second difference and the millisecond difference. And the last one is microsecond difference. Let's see the institute function. It checks for valid Denver loop. If it found the value is valid, then it returns one. If not, then returns 0. Here I'm using is did function in the fastest IT function. I'm using Git did, which returns really did temple. And the next is the function f given some number which is not added value. Let's execute times c. That is, it can't see the fastest dirty returned one as it is getting fairly did payment. And the second is that the TMS 0 as the input melody is a number. Let's talk about the E or one function, which takes any data input and it returns last day of the month. Let's execute it. And it didn't last day of TVD MM, which is tapped differs. 4. Converting Dates: Converting date datatype. The convert function is used for data type conversion. Here in this example, we are converting a string. Well, it is taking two parameters that target data type and the expression. In the fast step when we are converting the StringBuilder contents, same date, tame example. In the second lane. I'm converting good debt. Good day today. Let's execute it together. Once he bought the example, it returned that date, but similar to the above example, here we are converting to time datatype. Let's execute it together. You can see it returned the time part only, similar to our example, can convert steam will, one that did it at a lake here in example, we are converting a string to obtain and date time. And we had the attempt to, and the last one is dip them offset. Let's execute it. So the StringBuilder converted to dip them datatype. And the time part swing 0 as the NO times persuade in the string value. Now we'll see the opposite. Will convert did 10 million StringBuilder. For that, we will use the convert function. So it takes three parameters. That target data type. If you see the fast statement, it takes three parameters that target datatype and the expression or the ALU, and the formats, which is optional. But we can specify different formats fiscal year to get the string will send output stream well window comments, statements. Can see that based on format specifier to output string value will be formatted. Let's execute it together. You can see we receive different formatted string. Hello, Miss done the format specifier. 5. Conclusion: Thank you for watching this course. I hope you got some values from the menus. And do not forget to check out the excess below.