Language
Kevin, January 16, 2005 - 6:08 pm UTC
This site is an invaluable resource to many Oracle professionals and enthusiasts across the globe - thank you Tom.
It is absolutely correct to insist on correct syntax and grammar when asking questions referring to PL/SQL, java, etc, but surely you must accept that for those whose first language is not English (in any of its billion dialects), onomatapoeic language will suffice?
Your work for the Oracle community is truly amazing - and people all over the world buy your books and seek your counsel. It is obvious from the very existence of this page that people are finding Oracle obtuse enough without your critique on their foreign language skills as well.
January 16, 2005 - 7:32 pm UTC
hooked on phonics -- I can accept.
IM speak -- nope, not into it. onomatapoeic language is as hard to read on onamatapoeic is to type :)
This has nothing to do with foreign language skills -- it is all about writing using proper words. Would you/they like it if I wrote in that style to save keystrokes in a book?
It is a pet peeve and I gently point it out when I see it, which is alarmingly frequent. Drives me nuts.
It is obvious that the person writing this can write proper english -- typos are one thing, IM speak -- I'm afraid it is turning into a sublanguage all of it's own.
Loz, January 16, 2005 - 7:03 pm UTC
Kevin,
I agree with your comments about this site, but am in total disagreement with the language & grammar part.
Communication is a fundamental element of software design. This SMS-speak crap really gets to me (as it does Tom, I suspect, from the many comments to those who use it). Anyone who can understand why u r means 'you are' can write it properly. I get more sympathetic when it comes to the less obvious elements of English for the English-as-a-second-language guys. Even so, the more effort one puts in, the better one becomes.
I'll say it again. If you can't communicate clearly, you have no business in the world of software. That goes for asking for help as well as discussing requirements with clients and colleagues.
</rant>
Language
Narasimhan, January 16, 2005 - 7:21 pm UTC
Hi all,
I agree with both TOM and Kevin. However, the question remain the same, what is SLD? I can makeout "how r u" is "How are you", but I fail to make out, what is SLD.
I am sure, Tom is not pointing out at any grammetical errors. (I still do, in addition to spelling mistakes) However, It would be a lot better, if people stop using synonyms and use the correct words to help the world understand their question right away (the performance of understanding will improve, if own synonyms and short cuts are avoided). I am sure, we all can easily understand TOM's answers for the same reason.
The world appreciates his service and knowledge. It is impossible for any oracle person, not know this site.
We Can try a bit, to do better to put the right words in place, to make the question clear to all. Atleast, we can expand the synonyms at the end of the question, if it is used frequently in the question. I bet, not a single day would pass for many of us without using "copy and paste" technique, while working on the computer. Why not type once, and copy paste in the rest of the places.
Hope everybody will take it positively.
Thanks
'sld' =
Stuart, January 17, 2005 - 6:39 am UTC
I'm fairly confident that 'sld' means 'should' - not 100% which is why I'm not in favour of these SMS-isms either - don't even use them when I'm texting someone
On the subject of grammar - I've noticed Tom, that you use 'then' when I would have used 'than' - is this a bad habit or just a difference in American english versus UK english?
January 17, 2005 - 8:40 am UTC
it is a horrible habit that I've been trying to correct (hopefully in the last year you might have noticed a difference there).
Everytime I type then/than now, I look at it twice.
I'm trying really hard on that one.
Language - Redux
Kevin, January 17, 2005 - 9:38 am UTC
I just wanted to clarify my comments in the earlier review. I am definitely not a fan of "SMS" or "IM" speech - I am forever rejecting documents and emails that contain paragraphs that look as if they've had all the vowels removed :)
My only reservation is that the Internet (in particular, some less erudite forums than this ;) can give the impression that contracted words are acceptable, or even the norm for communication like this. Given this backdrop, I'm a little more forgiving when I see it. That's not to say it doesn't jar - it does!
But - hey! - it's your site, and I think it's excellent.
writing
Tom Jordan, January 17, 2005 - 9:39 am UTC
Tom,
speaking of writing habits and such, do you find it difficult to put technical subjects down in writing? I've read a few of your books and I truly enjoy your writing style. It's not "dry", like so many of the technical manuals I'm sure you've come across. When you're at your desk (or wherever) writing, what do you think about more, the actual technical subject at hand or the wording of it?
Thanks,
Tom
January 17, 2005 - 10:05 am UTC
I write about tech exactly in the same way I would talk about tech...
And I have some of the best in the world as technical editors -- that makes a huge huge difference (judge a book by it's technical editing team! They are in many ways as important as the person writing the book itself)
British legacy
Reader, January 17, 2005 - 10:26 am UTC
I 100% agree with not using IM/SMS in any communications. It takes me longer to figure out what the other person is trying to say! However, my sympathies/understanding goes out when i see people (specially from SE Asia) using 'Sir' in their writings! this is something that has been left behind by the british amongst other crap! Sorry, for using the wrong forum for this, but the use of such words reminds me of colonial times! and to know the Asian governments are still operating with 'colonial' mentality!
Leap years and end of month
Sarah, January 17, 2005 - 11:04 am UTC
I've been trying to break the query with no success...
It looks ok, but can you reassure me that leap years and months with different days are handled?
For example if I copy your test:
select add_months( to_date('28-feb-2004'), 1*12 )+0
from dual;
select add_months( to_date('29-feb-2004'), 1*12 )+0
from dual;
Both return 28/02/2005
It looks as though you can't uniquely determine the date of death from the date of birth, years and days. Is this correct?
January 17, 2005 - 11:15 am UTC
Well, 1 year from feb-28th is -- feb-28th the next year.
and 1 year from the last day of a month is the last day of the month in the next year.
You can uniquely dtermine the date of death from DOB, plus YEARS, plus DAYS.
it is just that you cannot go the other way :)
add_months works such that if the input date is the last day of the month, it will return the last day of the month.
if the input date is NOT the last day of the month, it'll return that day in the next month (or the last day in that next month if the last day possible is less than the one you were currently on)
One year from feb-28th 2004 is feb-28th 2005
One year from feb-29th 2004 (last day of the month) is by convention the last day of feb, 2005 -- which is feb 28th.
The problem is that "1 year" is a convention we use, it does not really mean a certain number of days.
Abbreviations
A reader, January 17, 2005 - 11:12 am UTC
Re: Narasimhan from Zurich, Switzerland: "the performance of understanding will improve, if own synonyms and short cuts are avoided"
That's one of the reasons why I read Tom's site through a proxy that replaces things like "dmt" with "<abbr title='Dictionary Managed Tablespace'>dmt</abbr>" :)
January 17, 2005 - 11:17 am UTC
I tend to avoid abbreviations but DMT and LMT should be comfortable enough for readers here -- it is in so many postings :)
but -- touche'
THANKS
Muhammad Asim, January 17, 2005 - 12:14 pm UTC
HELLO Mr. TOM,
many many thanks for your answer...also I am sorry for using shortcut words...I agree with you all people with regard to writing correct english...
also Mr. Reader from CT, USA ....
for me the use of word 'Sir' has nothing to do with Colonialism...but I use the word 'Sir' for my teachers and any one from whom I learn something....its just a mark of respect...many people and especially many sportsmen have been Knighted with the title of 'Sir'...u need to be a little more broad minded....
Regards,
Asim.
Easily offended?
Paul, January 18, 2005 - 11:02 am UTC
Tom's views on IM speak are spot on. If people are easily offended perhaps they should stay away from this site.
The articles here are excellent. Why should the majority (who understand written English) have to put up with the minority who want to impose something that can't always be easily understood?
The devil's advocate...
Kashif, January 18, 2005 - 2:44 pm UTC
Hi Tom,
I have much respect for your knowledge and your willingness to educate, however to be fair I must say that I've frequently come across postings by you that have acronyms such as IMHO, YMMV etc. Those can be equally confusing for people who are unaware of the meaning of these acronyms. Although I did find a site which others might find useful when looking up such acronyms:
</code>
http://www.computeruser.com/resources/dictionary/noframes/nf.chat.html <code>
Other than that, rock on...
Kashif
January 18, 2005 - 3:39 pm UTC
I found 4 maybe 5 YMMV's (old) out of 25,000 postings (and have sworn off of it for a long time - about the first time someone said "YMMV what is that") (there are 11 articles on this site with YMMV, most of the time, I didn't say it)
and most of the IMHO's -- not mine.....
and not within the last 2 to 3 years..
So, to say "i've frequently", I'd need to see a couple dozen examples. I searched for IMHO sorted by timestamp (posting date) and the first 10 -- not me. Perhaps in the BODY of the document sure, but that doesn't mean I put it there. Doesn't mean you won't find some, but you won't find "frequently" and you won't find "recently" (i hope)
As I've traveled lots more internationally in the last 4 years than in all of my prior years - I've learned the importance of preciseness in speech and communication. My favorite example, I have a slide:
Audit is not a four letter word
Benchmark is not a four letter word
Instrumentation is not a four letter word
Tune -- Tune is a four letter word.
Now, many native English speaking people recognize my point there -- they understand what a four letter word is.
In much of Europe, where English is the second language, that means nothing.
Analogies, Abbreviations, Local Sayings (not everyone knows what "6 one way, 1/2 dozen the other" means -- they don't know what a dozen is, like I don't know what a lakh is) -- they don't work here.
Agreed.
Kashif, January 18, 2005 - 4:39 pm UTC
Hi Tom,
Agreed, I did not look at the time of the postings too closely to determine when you had posted those comments, nor did I do a count on the number of hits I got, I was recalling from memory. I tend to forget that many of your postings are quite old. Incidentally, the "lakh" refers to 100,000, 2 lakhs would be 200,000 etc. Though it is not an English word at all, it is a Hindi/Urdu word, so its use on this site is even less appropriate.
Kashif
P.S. Not to be a nuisance but here's another one I have looked up in the past: ROTFL!
Let's close this thread here itself
A reader, January 18, 2005 - 9:10 pm UTC
difference in a single column
Muhammad Asim, January 23, 2005 - 1:12 pm UTC
hello Tom,
with reference to my original question and your answer
which was...
ops$tkyte@ORA9IR2> select abs(trunc(months_between( d1, d2 ) /12 )) years,
2 greatest(d1,d2)-add_months( least(d1,d2),
3 12*abs(trunc(months_between( d1, d2 ) /12 )) ) days
4 from
5 (
6 select to_date( '25-jan-1910' ) d1, to_date('17-jan-2005') d2
7 from dual
8 )
9 /
YEARS DAYS
---------- ----------
94 358
I want that the desired output for the same calculation which is at this point coming in seperate 'Year' and 'Days' columns should come in a single 'Difference' coulmn..in exactly the format like the one given below..
Difference
94 years & 258 days
..the words 'Years' and 'Days' should come along with the numbers...and if the difference is one year or one day the output should be
Difference
1 year & 1 day
thus not including the 's' with 'year' and 'day'
will you please help?
regards,
Asim.
January 23, 2005 - 1:47 pm UTC
case and concatenation.... should be pretty simple....
select years ||
case when years = 1 then ' year ' else ' years ' end ||
' & ' ||
days ||
case when days = 1 then ' day' else ' days' end
from (that_query)
in sqlplus, set define off if you want to use the &
thanks
Asim, January 25, 2005 - 6:23 am UTC
yes, done ..
many thanks
regards,
Asim
add months to date : limit cases
laurent schneider, April 26, 2005 - 6:28 am UTC
Hi,
just a small remark. years and months are not constants, so it is always possible to find limit cases.
Ex: if you are born 28-feb-1900, how old will you be on 28-feb-2000? 100 years? 100 years - 1 day? And if you are born 29-feb-2000, how old would you be on 28-feb-2100? 100 years? 100 years - 1 day?
If I am born 28-feb-2000 and my brother 29-feb-2000, will we have the same age on 28-feb-2001?
I think an acceptable answer would be, if you are born 29-feb-2000, you will be 365 days old on that date. But if you are born 28-feb-2001, then you will be 1 year 0 day.
kind regards
leap years
laurent schneider, May 17, 2005 - 7:30 am UTC
Here is a suggestion : getting years difference with to_char to avoid add_months problem for leap years, then adding the years with add_months to the first of the month for the dob, then adding the day of dob-1 then substracting 1 if there is no birthday in dod year (logic is there is 1 year and 1 day between 2004-02-29 and 2005-03-01)
select
dob,dod,
trunc((to_char(dod,'YYYYMMDD')-to_char(dob,'YYYYMMDD'))/10000) years,
dod-(add_months(trunc(dob,'MM'),12*trunc((to_char(dod,'YYYYMMDD')-to_char(dob,'YYYYMMDD'))/10000))+extract(day from dob)-1-
decode(extract(month from add_months(trunc(dob,'MM'),12*trunc((to_char(dod,'YYYYMMDD')-to_char(dob,'YYYYMMDD'))/10000))+extract(day from dob)-1),
extract(month from dob), 0, 1)) days
from
(select date '1904-02-28' dob from dual union select date '1903-02-28' from dual union select date '1900-02-28' from dual union select date '1904-02-29' from dual),
(select date '2003-02-28' dod from dual union select date '2003-03-01' from dual union select date '2000-02-28' from dual union select date '2000-02-29' from dual)
where dod>dob
order by 1,2
/
DOB DOD YEARS DAYS
----------- ----------- ---------- ----------
28-FEB-1900 28-FEB-2000 100 0
28-FEB-1900 29-FEB-2000 100 1
28-FEB-1900 28-FEB-2003 103 0
28-FEB-1900 01-MAR-2003 103 1
28-FEB-1903 28-FEB-2000 97 0
28-FEB-1903 29-FEB-2000 97 1
28-FEB-1903 28-FEB-2003 100 0
28-FEB-1903 01-MAR-2003 100 1
28-FEB-1904 28-FEB-2000 96 0
28-FEB-1904 29-FEB-2000 96 1
28-FEB-1904 28-FEB-2003 99 0
28-FEB-1904 01-MAR-2003 99 1
29-FEB-1904 28-FEB-2000 95 365
29-FEB-1904 29-FEB-2000 96 0
29-FEB-1904 28-FEB-2003 98 365
29-FEB-1904 01-MAR-2003 99 1
kindest regards
May 17, 2005 - 9:13 am UTC
what problem is there with leap years??
The problem is that "1 year" is a convention we use, it does not really mean a
certain number of days.
leap year
Laurent Schneider, May 18, 2005 - 5:40 am UTC
if you are born 28-02-2003, you will be 1 year and 0 day on 28-02-2004, will not you?
if you are born 29-02-2004, you will never be 1 year and 0 day.
Of course "year" is a convention. You can also say that each month have 30 days and the year have 360 days. This will for example ease the calculation of moregages and interests.
In my humble opinion, if you have to determine if someone is 18 or older (legal), you cannot use add_months.
regards
May 18, 2005 - 9:08 am UTC
you absolutely can use add_months.
You tell me, if you are born on 29-02-2004, when do you turn 18?
ops$tkyte@ORA9IR2> select to_date( '29-02-2004', 'dd-mm-yyyy' ) from dual;
TO_DATE('
---------
29-FEB-04
ops$tkyte@ORA9IR2> select add_months( to_date( '29-02-2004', 'dd-mm-yyyy' ), 18*12) from dual;
ADD_MONTH
---------
28-FEB-22
the last day of feb, 2022. It is *the convention*.
1 select abs(trunc(months_between( d1, d2 ) /12 )) years,
2 greatest(d1,d2)-add_months( least(d1,d2),
3 12*abs(trunc(months_between( d1, d2 ) /12 )) ) days
4 from
5 (
6 select to_date( '29-02-2004', 'dd-mm-yyyy' ) d1,
7 add_months( to_date( '29-02-2004', 'dd-mm-yyyy' ), 18*12) d2
8 from dual
9* )
ops$tkyte@ORA9IR2> /
YEARS DAYS
---------- ----------
18 0
<b>
People can be different numbers of "days old" on their 18 birthday.
See, I was lucky being born in 1965:</b>
ops$tkyte@ORA9IR2> select abs(trunc(months_between( d1, d2 ) /12 )) years,
2 greatest(d1,d2)-add_months( least(d1,d2),
3 12*abs(trunc(months_between( d1, d2 ) /12 )) ) days,
4 d2-d1 total_days
5 from
6 (
7 select to_date( '15-03-1965', 'dd-mm-yyyy' ) d1,
8 add_months( to_date( '15-03-1965', 'dd-mm-yyyy' ), 18*12) d2
9 from dual
10 )
11 /
YEARS DAYS TOTAL_DAYS
---------- ---------- ----------
18 0 6574
ops$tkyte@ORA9IR2> select abs(trunc(months_between( d1, d2 ) /12 )) years,
2 greatest(d1,d2)-add_months( least(d1,d2),
3 12*abs(trunc(months_between( d1, d2 ) /12 )) ) days,
4 d2-d1 total_days
5 from
6 (
7 select to_date( '15-03-1966', 'dd-mm-yyyy' ) d1,
8 add_months( to_date( '15-03-1966', 'dd-mm-yyyy' ), 18*12) d2
9 from dual
10 )
11 /
YEARS DAYS TOTAL_DAYS
---------- ---------- ----------
18 0 6575
I was 18 one day before someone born a year later.
convention leap years
Laurent Schneider, May 19, 2005 - 5:44 am UTC
I tried some research on the internet and it seems that leap year babies will have your 18th birthday in March (like you and me).
scottland: Age of Legal Capacity
</code>
http://www.hmso.gov.uk/acts/acts1991/Ukpga_19910050_en_2.htm Attainment of age
6.(2) Where a person has been born on 29th February in a leap year, the relevant anniversary in any year other than a leap year shall be taken to be 1st March.
canada: retirement office (cr9607)
Born 29 february : The birthday of a person born Feb 29th is Mar 1st the non-leap years.
dob date age
1936-02-29 1995-02-28 58,997 http://www.rrpepul.ulaval.ca/Interpretation/age.html <code>(in french)
best regards
May 19, 2005 - 8:11 am UTC
convention leap years
Laurent SChneider, May 19, 2005 - 9:52 am UTC
yes, it is well said. It may be different in each juridiction. I looked in my country (admin.ch) and found nothing. Probably it is so rare that someone born Feb 29th want to marry -or/and drink alcohol- at the age of 18 on Feb 28th that noone care...
However, I am fairly certain that someone born 28 Feb will always have his birthday on 28 Feb.
Ok, I find the discussion you referred very interresting and I am definitely agreeing it is a convention.
Cheers
How do you find a leapYear?
A reader, December 08, 2005 - 12:58 pm UTC
Tom, If i have a bunch of dates for a date column in a table, how do i fetch out only dates that are leap year dates
eg:- 02/29/2000 or 02/29/2004 etc..
Thanks,
December 09, 2005 - 1:15 am UTC
where to_char( date, 'mmdd' ) = '0229'
would work - no easy way to do this. (eg: unless you have a funtion based index, this will have to "scan".
m_nasef, May 11, 2010 - 6:52 pm UTC
* select the employees who were hired in the last 28 yrs :-
SQL> select *
from emp
where months_between(trunc(sysdate,'Y'),trunc(hiredate,'Y'))-(28*12)=0
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
thanks,