如何从存储的字符串日期打印十进制数字(PL/SQL) [英] How to print decimal number from stored string date (PL/SQL)

问题描述

试图解决一个任务，我有一个由车主组成的桌子.我应该写一个匿名的PLSQL语句，在其中应打印名字，姓氏和所有者的年龄，例如如果他们的出生日期是'19801109'，则应将其转换为数字，并带有小数点，例如' 34.4岁".怎么做? (您可以想象的，这是非常新的). 这是我的代码，没有任何用于转换/计算的功能:

Trying to solve one assignment where I have a table consisting of owners of cars. I am supposed to write an anonymous PLSQL statement where I should print first name, last name and how old the owners are, like if their dob is '19801109' it should convert that to their age in a number, with one decimal, like '34,4 years old'. How to do that? (very new to this as you can imagine). Here is my code, without any function for the conversion/calculation:

declare
cursor c_owners is select initcap(fname), initcap(lname), dob
                from car_owner;
v_dob car_owner.pnr%type;
v_fnamn car_owner.fname%type;
v_enamn car_owner.lname%type;
begin 
  if not c_owners%isopen then
  open c_owner;
  end if;
     loop
     fetch c_owner
     into v_fname, v_lname, v_dob;
     exit when c_owners%notfound;
     dbms_output.put_line(v_fname||', '||v_lname||', '||v_dob||'year.');
     end loop;

close c_owners;
end;
/

如果有人可以帮助，那将会很棒.现在被卡住了一段时间.我知道我可以使用更好的编写方法来减少代码，但尝试首先学习基础知识:) 结果应如下所示:

Would be awesome if someone could help. Been stuck with this for some time now. I know I could use better ways of writing to get less code but trying to learn the basics first :) The result should look like:

约翰·约翰逊(John，Johnson)，34.4岁.

John, Johnson, 34,4 years old.

当我问一个朋友，他做了这样的事情:

When I asked a friend he had done like this:

begin 
for rec in select fname, lname, round (months_between(sysdate,  to_Date('19'|| 
SUBSTR(dob,0), INSTR(dob, '-')-1)'YYMMDD')) as age 
from car_owner; 
loop dbms_output.put_line(initcat(rec.fname) || ', '|| initcap(rec.lname) ||' , '|| rec.age||' age'; 
end loop; 
end;
/

但是那没有用(不管我怎么做都给右括号加上错误)，而且我还没有完全理解它，但是也许它可以帮助您了解它的外观?其在表格中约有10行，并且输出应根据其Dob打印每个年龄.

But that did not work (annoying right parentheses error no matter how I did), and I did not understand it fully, but maybe it helps you see how it should look? its about ten rows in the table and the output should print each ones age based on their dob.

推荐答案

有关在C#中获得精确年龄的好答案是: 以给定日期时间的十进制精度表示的年龄

There's a nice answer about getting a precise age in C# here: Age in years with decimal precision given a datetime

已翻译为PL/SQL:

Translated to PL/SQL:

declare
  v_dob varchar(8) := '19801109';
  v_dob_as_date date := to_date(v_dob,'yyyymmdd');
  v_current_date date := sysdate;
  v_years integer;
  v_last_birthday date;
  v_next_birthday date;
begin
    v_years := extract(year from v_current_date) - extract(year from v_dob_as_date);
    v_last_birthday := ADD_MONTHS(v_dob_as_date, 12 * v_years);
    if v_last_birthday > v_current_date then
      v_years := v_years -1;
      v_last_birthday := ADD_MONTHS(v_dob_as_date, 12 * v_years);
    end if;
    v_next_birthday := ADD_MONTHS(v_last_birthday, 12);
    dbms_output.put_line('Years old (whole number): ' || v_years);
    dbms_output.put_line('Last birthday: ' ||  v_last_birthday);
    dbms_output.put_line('Next birthday: ' ||  v_next_birthday);
    dbms_output.put_line('Days between last and next birthdays: ' ||  (v_next_birthday - v_last_birthday));
    dbms_output.put_line('Days since last birthday: ' ||  (v_current_date - v_last_birthday));
    dbms_output.put_line('Exact age: ' || round(v_years + ( (v_current_date - v_last_birthday) / (v_next_birthday - v_last_birthday)),1));
end;

结果(SYSDATE为15年5月21日上午10:45:11)为34.5.您可以通过更简单的过程获得相同的结果:

The result (with SYSDATE being 21-MAY-15 10:45:11 AM) is 34.5. You get the same thing with the much simpler procedure:

declare
  v_dob varchar(8) := '19801109';
  v_dob_as_date date := to_date(v_dob,'yyyymmdd');
begin
  dbms_output.put_line('Age: ' || round(months_between(sysdate,to_date('19801109','yyyymmdd'))/12,1));
end;

如果四舍五入到小数点后五位，结果将有所不同.第一种方法产生34.52999，第二种方法产生34.53346.

If you round to 5 decimal places, the results vary. The first method produces 34.52999, and the second produces 34.53346.

这篇关于如何从存储的字符串日期打印十进制数字(PL/SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！