Urge Date Field with airSlate SignNow

Reveal date field

in this section let's look at oracle state functions and see what kind of queries you generally fire and what functions you usually use when when you run sequel statements which have date related logic in them the first function that i want to disk is is something that we've already seen when we discussed functions in their inputs it's the state function so when i say select state from dual i get the current system later the current database date which is 30th november 2013 there is already an important point to understand here when we say oracle state data type it actually has both the date plus the time after the seconds now one might wonder why we are not seeing the time component over here when date has the time component the reason for that is again the session level settings that we currently have the display level settings so let me first prove that this doesn't have this actually has the time component so if we say where state is equal to to date of 30th November 2013 so technically if it doesn't have the day this should be the same but if you see here the nor it nor Rose return which means this condition has actually failed so how do we make sure that we see the entire date including the time component as well there are multiple ways to do it one thing that is that is more of a programmatic way to do it it's a is altering the session to show the date format and you want this is again something that we have done in a really old video when it comes to filters but let's do it again over here so you can say alter session set nls date format is equal to DD mo n DD MN or Y by H H I want to see the data in 24 hour format mi SS so if you can see here it says the session has been altered now when I say state from dual as long as I am logged in in this particular session I will see the time component as well it says 943 basically and because it is in the 24-hour format so this is how we confirm that Oracle has the date format so what happens when let's say I disconnect this session and again run it and again connect I'm going to use the OE over here and when it on the same thing again the settings have been reset so when you're running specifically inside a script for example you always if you want the date format to be in a specific way in the beginning of the script you can put this condition saying you can put this command saying order session my date format should always be this when you're working in sequel developer though you have this option inside preferences tools preferences go to database and then nls and here you see that the date format is DDM ONRR what I also want to see actually I want to set this to DD m1 why why why why h/h 24 in my missus so this is the date format that I am most familiar with and I want data to be in displayed in this particular format so when it will ok and say selects is date from dual you see the data in the format that you need once again you but you need to remember is internally the date data type is represented pretty much the same it's a inbuilt data type what we are changing here is only the way it is displayed when you're running a query so a lot of times when users query data they might also have this confusion saying hey I'm saying my where clause or my where condition is equal to so and so date when I see the data here it says 30th November but when I said the date equal to 30th November I get no results the reason for that is this date that they're seeing here is not the complete date it also has the time component and you need to make sure your queries are changed to adjust to that particular factor the second important thing to remember is that Oracle's date arithmetic works at day level what does it actually mean so here we have the C state as 30 M 2013 let's say I want a date which is one day ahead of this so I want to add one day to this particular day I can just do sustained +1 and instead of xxx I get first December at the same time so all we are adding here is just exactly adding 24 hours to this particular day so let's say instead of 24 hours I just want to add one hour so in that case you could do 1 by 24 here you get the same date with one hour added if I want to add one minute because an hour as 60 minutes you can say by 60 and if you want to add one second you do another 60 out after so once you understand Arcas data dramatic works at a day level writing queries like this becomes really simple so for example what I could do is if somebody asks me what are the orders that have been placed in the last 30 days so I could say select star from Oh a dot orders where order date is greater than 30 a day which is 30 days in the past so you can say this game current name - critic anything placed after that you want to see those orders so this is how you would do plain simple data arithmetic inside Oracle trunk is another really useful function when it comes to date arithmetic and running queries where your which are based on dates so we've seen the example of trunk being an overloaded function in the numbers chapter in the case of numbers when you did shrunk it removed the decimal points up to whatever you specified in the case of dates it works very much the same way but instead of removing a number it removes a particular date or time component for example when you say select state from do you get all the you get the time component here by default when you say trunk Oracle gets rid of the time component and gives you that date at 0 0 hours just like numbers here also you can pass in multiple inputs to say till what part you want the date to be truncated for example the default here is till till the date component you could say I want data to be truncated till the month component so the instead of 30th November Oracle gives you a date back saying first number of 2013 so the entire month part has been truncated now and you get the first of the month you could do the same thing with here as well and here you get in the entire years component or the entire years part of the data strung cater and you get 1st Jan 2013 so this is how you can use the trunk function inside dates and I hope this gives you a decent overview of how overloaded functions work the same function depending on the data type will behave differently again let's say this is really useful when you're running queries where you want to see the orders that are placed in the year 2013 so you can do two things one is you can say select star from way down orders where order date greater than to date 0 1 Jan because 13 so this query I'm sorry so this query works as well the problem with this is this query works only for only for now let's say we go into 2014 then you would probably want to see all the orders that have been placed after first Jan 2014 and you probably changed want to change the query again this is not a very reusable query in that in that sense so what we actually can do we say trunk of state come why why why why now when you run the query you see all the orders that are that have been placed after 2013 when you run the same query on any date in 2014 you see all the orders that have been placed after first Jan 2014 so in that sense it is a reusable query where your query condition actually changes based on when you're running it so instead of passing in fixed dates this is more of an approach that you will see in a lot of scripts in a lot of sequel processes that are at your company or the sequel process that you write this is probably more of the approach that you would take to write generic scripts this is also another really important reason why you should clarify requirements when somebody is asking for a set of data for example when somebody asks data that have been placed in the current year or all the orders that have been placed in the past year or so it's not really a very precise requirement you need to specifically ask them are you looking at data that is all the orders that have been placed in the last 365 days or everything that has been placed in the year so far because if they're looking for years orders that have been placed in the year so far then this is the right function if they're looking at orders that have been placed in the last 365 days then you might want to say sustained minus 365 the output for these two queries is substantially different so you need to really confirm the requirements and say what kind of data that you want to see before you write the query for that particular question we just saw examples of adding minutes or seconds or or days to a particular date that was a little easier because every date every every day has 24 hours and every hour has precisely 60 minutes but what do you have to do when you are trying to add one month of been trying to get date which is one month ahead from now or one year ahead from now for example let us say if I say select see state plus 30 from dual this is a good enough way of saying the next month all the time it's not because a month doesn't necessarily always have 30 days it might have 31 it might have 28 or it might have 29 the same thing goes for ears as well you can't always add 365 days to the current date and say well this is the next year state in the same fashion sometimes it works sometimes you might have 366 days in which case your logic is messed up so to help with this logic where based on the ear or based on the month you add different dates or will has some inbuilt functions provided one of them is add months so the function takes in two parameters one is a cat one is a date and the other is the number of months that you want to add to it for example required three months to see state you will get 28 February 2014 though currently it is 30th November it will give you the last name of that particular month and adjust it accordingly to get the correct date you could also say that I want to add two years which is 2 into 12 months and you get 30th November of 2015 so this is another example of where before you write a function by yourself before you write an entire logic in your PL sequel function you should look for Oracle functions which are already providing the logic chances are if it's a decent enough requirement if it's a common enough requirement Oracle already has a function for you so we've seen examples of how you can add different types of intervals where you can add a day a month a year or a second and so on using somewhat of a rudimentary method where for adding a particular second you have to say 1 by 24 by 60 by 365 and so on and so forth the reason why I explained them to you is because there might still be a lot of scripts a lot of logic which uses on those old-fashioned methods in your code and to understand that you need to understand or can state arithmetic but often late Oracle has also Oracle also provides a couple of very useful functions really simple to understand functions which to add or subtract any amount of days or any amount of time intervals to a particular date and though our and those are the num to come to D s interval and norm to ym interval this function here it converts our given number to do anything between a day in a second so if you want to add a second and our order date to a particular date then you can use this function if you want to add a month or a year you can use this function so the usage of this is really simple let's say if you want to add select sustained let's see state and the new column side-by-side so that we get we can confirm the results as well and then you say sustained now I want to add an interval to it let's say I want to add five hours to it you can same num2 d s interval five hour so in this case I get 9:57 is the time here and here the after adding five hours it's 1457 the same thing can you can do with everything up to a dates so I can do seconds hours minutes and of course days so this kind of helps you avoid all that clumsy logic where you say 1 by 24 1 by 24 by 60 and so on but it's always good to know that trick as well because there are there is certain there is some code out there which already uses that that logic the second one is pretty simple you can add months or years using num2 i am interval so you can say add five months you get data which is five months from now and you can say add five years you can data which is five years from now so this is how you can do almost pretty much all the other data and metric when adding intervals or two dates in Oracle using these two functions so far we've discussed about eight to nine different Oracle daytime functions and the easiest way to really get familiar with this to really understand this and know which function to use when is writing as many queries as possible and you also need to remember that this function does not have each statement or each expression doesn't necessarily have to be used only once in a particular function or an expression doesn't necessarily have only one particular date or character any such function used more often than not you will probably use multiple functions on the same expression to get the data in the in the format that you need so let's take a couple of examples where you have to use multiple functions and see how we'll go about getting data for them so let's try and solve a problem where we can use different functions that we've learned so far things like truncate on a date things like aggregate functions and so on and so forth one common example is when you want to get a yearly breakdown of your total sales so in my table orders so let's start from your own orders I have the order date saying 16th August 1999 16 19 November and so on and so forth and I have the order total but what the business wants to see is see for a given year for example in 2000 what is the total order amount let's say it is 200,000 in 1999 what is the corresponding order them on something like this so this is the format that they want to see in where the data is aggregated and you get one total per year so how do we go about it the first thing that immediately should strike is that in the orders table you have for a year you have hundreds of records but what you want to do is consolidate all of them into one record per year which means you need to use a grouping function so let's try writing the group by here group by I want to see order date and here I want to say order date and the total value of my orders so when you run this instead of seeing one row per year what you see here is that you see one row per order time this is because when you're grouping by these different orders have different times they have different dates all we're looking for is aggregating data to the air level so for this particular example we need to remove all the other components other than the year component so that's why we would say trunk bye bye bye bye and then even the group bye we need the same expression chunk of order date going by one now as you can see except for the ear part everything else in the date has been truncated off so you see first chan of that ear and the corresponding sales so this is all the sales which happen in 1999 these are sales which happened in 2000 and so on and so forth so let's actually order these by the ear descending because it will make it will actually make more sense so trunk descending in this case again if somebody is looking at this data it might not make as much sense or you might be conveying some wrong information saying on the first Jan 2011 of orders that have been placed this is a little incorrect it's not really accurate what we're trying to say is that in the year 2000 this is the total set of this a total amount of orders that we've got so here we only want to display the year let me put the same thing in the group by and other places inside my sequel as well you there you have it the breakdown of the total amount of orders that we've got the total revenue from each of from all the orders in a particular order by the ear descending you can of course label them in however you want to make the data look more meaningful but this is how you can use multiple functions some with date some with character some with grouping to get the data in the format that you need again here in this case we have used a function called two card which is actually a conversion function which we'll be discussing next but this is just to show that you can use multiple functions together to really effectively get the data in one particular format another example that I want to go through which kind of requires you to use multiple functions is generating monthly statements for example let's say currently I am working in a company where I want to say all the states that have happened in the last month so when any time during this month any time during the month of November 2013 when I run a report I want data from first October 2013 to 31st October 2013 summarized according to whatever requirements I have maybe it is the total orders maybe it is total revenue total profit whatever the requirements are but in any time during November when I run I need these two dates as the beginning and end date for my data so the one thing that you can do is let's say when learning assist aid from dual I get 30th November so how do we get the first of previous month so you can say admins of C state comma minus 1 which will give you the same date of the previous month and when you put a trunk truncal month you get the first October 2013 which is the first day of the previous month you can do the same thing with last day of last day of previous month as well maybe you could do you can get chunk of this month and then do last day which is another orbital function we say last day of that month you get 31st October 2013 and in your query now you can say data between chunk of this which is the first October and last day of this which is the 31st October and you'll get data in the time range again there are various ways of implementing the same problem so instead of last day I can use this is well here is a trunk of cyst 8 comma ml which will give me the first date of the current month and I could say minus one here which will again give me the last day of the previous month so there multiple ways of running the same of getting the same data implementing the same logic and you can verify them by running statements against DLN and seeing how they are and then change your queries to use these particular variables or these particular computations