State function we know date arithmetic how does that work if you subtract two dates if I had a date of September 10th minus September first I get the number of days between them there would be nine days between them assuming it’s the same time okay because it’s a fraction of the times are different if you want to get rid of the fraction you can trunk it or you can uh you can trunk the actual dates that you’re subtracting I can say trunk of this state- the trunk of that they’ll get rid of the fractions or I can say this state- that data and trunk the whole thing and it’ll get rid of the fractions, either way, depending on how you want to operate if you take subtract the number today’s date minus ten will give you the date 10 days ago at the same time and if you add a number today’s day plus 10 will give you the date in 10 days from now at the same time.
So numbers with dates always add and subtract that number of days and as we mentioned before if you want one hour you have to do one over 24 you want one minute you will do / 1 / 24 x 60 one second you do one over 24 x 60 x 60 there are no eight hours eight minutes eight seconds you have to actually do it with a number and do your own arithmetic there although some other languages have that oracle does not have that okay let’s look at some code here I’m doing a to date Jun 8th at 12 and I’m doing a to date on that so I’m converting it to a date and I’m using date a month month month year each age 24 minute and second, so this is like 12 in the 24-hour clock which is going to be 12 p.m. okay and ten minutes after twelve and so on and then minus the two and the keyword as is not their notice how it looks kind of funny without the keyboard as so I mean I would have put the key word as in here as subtract two days because I want to have a column heading over here of subtract two days.
So that’s just my column heading so it’s two days before Jun 2 and therefore that’s Jun 1 May 31 at 1210 a half a day later is Jun 3 at ten minutes after midnight because this was 12 10 in the afternoon because it was a 24-hour clock so this is the next day at ten minutes after midnight instead of 10 minutes after noon the previous day if I only want to add six hours this is 12 in the afternoon so this is going to be 18 in a 24-hour clock 6pm 10 minutes after six and I can go and say last name hire date and to date of the date and I format the date- hire date so I’m looking at from the hire date until June second, 1996 how many days was that person employed well these people were employed after that because the june two they were hired before june to these people were hired after june two that’s why if you do Jun 2 minus the hire date you get a negative any questions on that okay well subtracting and aiding the number of days works four days it works four weeks.
Because you just multiply it by seven it works for hours you / 24 what does it not work for i can add minutes our seconds days weeks what it what can I do well here’s years are not too good and months are even worse years I can do 365 but you know I got a problem here you know is it a leap year is it not a leap year and months if I add 30 days and I really it’s a it’s a fourth of this month do I want to get the fourth the next month well you know 30 days may or may not be the fourth the next month depending on how many months are in how many days are in this month so Oracle does fortunately give us some functions for that and the fruit and the main one is months between well not that’s not the main one there are a couple of them then main one is the second one this is months between which just subtracts two dates it takes a start date and an end date in your book on page 2 207 you see where it says months between start date and end date this is a typo it the first parameter is the end date.
The second parameter to start date so these two should be reversed on page 207 but you’ll see in the examples that we’re going to do the first one is the end date and here’s an example of let’s see select months between the end is March 29th the start is februari 28th so from the start to the end looks like about one month and in the end a day something like that and this is a query to and query to will show a one-point 03 a little over one month in a day that’s why the first argument there is the end date, not the start date as it set up here it’s reversed alright so that’ll just tell you how many months between two dates the more useful one is the ad months function because we don’t know how many days when we add 30 days you know we would have to do a whole big calculation will Oracle does the whole thing for us and if the ad months you give it two arguments the start date and the number of months you want to add and Oracle will figure it out it’ll automatically be computed.
It knows how many days are in every month it knows when those leap years it knows all that nice stuff and let’s see if we have that the collision what calculation is it doing to come up with that one point zero three hours the 1.03 back here in this example it’s looking at the second element this is the second one and it’s saying from februari 28th to march 28th is one month but there’s an extra day therefore much 29th so the calculation is when it has to get a part of a month the default is that Oracle assumes is 30 days in a month and it gives you like a calculation we got one more day so its 130th that’s why it’s the point 0 3 whatever so the poetry is it / 31 is it / 28 so that one day say that again like I’m sorry maybe I’m just the one note how you get the 03 what is it what are you / to get the or 3 200 586 that’s just 138 130th of a month because it’s one month from februari 3828 to march 28th is one month but there’s one more day left right yes so it says okay.
I got to give you one more day and I’m assuming 30 days in a month so, therefore, I’m going to give you one 31 / 31 / 30 is the point 0 3 whatever Oh 3 2 2 and so on when / 30 is that so it’s one month plus 1 thirtieth of a month we okay with 1 / 31 no I’m not 1/30 is point 3 3333 that’s 1/3 is point 3 333 well the same as point zero three three three three you’re not going to get so what you have there is point are 225 you are not dividing by two you are right so it must be it must not be / 30 it must be let’s see it is 0 so 0 point 0 3 22 alright so let’s try that so it’s 1 / 03 something how about 30 158 OKs 06 / 31 31, not 30 31 you are correct okay good and maybe I’m not sure because the book actually said 30 and I never checked that oh here this is 31 days later okay by 31 multiplying this by 31 returns the one so it’s 31 you’re right it’s based on the 31-day month I said it wrong good catch thank you so we’ll always so I think.
So if you do not example for example if you do not Marsh for example if the length is 50 days it will divide by 50 the denominator it will be the length I think it’s not I’m saying uh so we I don’t think so it’s got to figure out what percentage of a month it is if it’s only one day it’s not going to give you a bird that would give you a much smaller fraction it’s got to be some percentage of the month I don’t know if it’s always 31 or it’ll erode into the right you’re right I think I didn’t send that right yes okay so yeah click on the sinkage ok ok ok now the eight months function was the start date and we had the number of months so that’s a really cool thing here’s these are just months between always gives you the fraction here zayed months so add months april seven two thousand nine add one month I get May seventh, 2009 if I do December 31st and add two and a half months I get December 31st and EDD 2.5.
I get how does that make any December 31st January thirty 1st february2014 integer alright so you can’t you don’t add two and a half months you just add a number an integer number of months and it will truncate this by default add two months and get you at the rate when the now notice that December 31st would two months go to February 31st there is no February 31st it does not go to the next month the March it stays in February and will give you the last day of that word either the 28th or 29th depending on whether it’s a leap year and here’s April seventh- 12 months is April seventh in the previous year 2008, okay and there were other some other functions that are of marginal use the next day function takes the start date and the day of the week Monday Tuesday Wednesday Thursday and gives you the next state the next Monday after the start date the next Tuesday sometimes in certain applications you need to know that.
So what we’ll figure that out for you is your next day January first, 2009 you want the next Tuesday after that it turns out to be on January six 2009 could have been January second or third depending on the year and so on okay alright so that was examples of the these a character number and date functions.
You have to wait 15 seconds.
This is a bast traffic exchange site. In this, your website's ad count is there.
From this side you can get a lot of help in boosting your website.
People search how to bring traffic to our website without SEO
It is a good way to get traffic to the website without SEO.