List Users Over a Certain Age

Question

I'm trying to list all the users who are above the age of 13 and have downloaded an app in the category "Social". With the users date of births in ascending order.

Below I attempted to convert users date of births into an int number and use that to only display users over "13" but to no avail.

My code :

SELECT DISTINCT
    CAST(u."Username" AS varchar2(20)) AS "Username",
    CAST(u."FirstName" AS varchar2(15)) AS "FirstName",
    CAST(u."LastName" AS varchar2(15)) AS "LastName",
    TRUNC((sysdate - u."DateOfBirth")/365.25) AS "DateOfBirth" 
FROM BR_USER u, BR_APPCATEGORY ap
WHERE ap."CategoryName" = 'Social' AND "DateOfBirth" > '13'
ORDER BY "DateOfBirth" ASC;

The error I receive :

ERROR at line 7:
ORA-01840: input value not long enough for date format

CREATE TABLE "BR_USER"(
     "UserId" NUMBER(6,0),
     "Username" VARCHAR2(35) CONSTRAINT "BR_USER_USERNAME_NN" NOT NULL ENABLE,
     "FirstName" VARCHAR2(30),
     "LastName" VARCHAR2(50) CONSTRAINT "BR_USER_LAST_NAME_NN" NOT NULL ENABLE,
     "Email" VARCHAR2(100) CONSTRAINT "BR_USER_EMAIL_NN" NOT NULL ENABLE,
     "Gender" VARCHAR2(3),
     "JoinDate" DATE CONSTRAINT "BR_USER_JOINDATE_NN" NOT NULL ENABLE,
     "DateOfBirth" DATE CONSTRAINT "BR_USER_DOB_NN" NOT NULL ENABLE,
     "CountryId" NUMBER(3,0),
     CONSTRAINT "BR_USER_EMAIL_CORRECT" CHECK ("Email" like '%@%'),
     CONSTRAINT "BR_USER_EMAIL_UNIQUE" UNIQUE("Email"),
     CONSTRAINT "BR_USER_ID_PK" PRIMARY KEY ("UserId") ENABLE
     )
/
CREATE TABLE "BR_APPCATEGORY"(
     "AppCategoryId" NUMBER(2,0),
     "CategoryName" VARCHAR2(20) CONSTRAINT "BR_APPCATEGORY_NAME_NN" NOT NULL ENABLE,
     "Description" VARCHAR2(100),
     CONSTRAINT "BR_APPCATEGORY_PK" PRIMARY KEY ("AppCategoryId") ENABLE
)
/

Does this answer your question? [Oracle Age calculation from Date of birth and Today](https://stackoverflow.com/questions/3015431/oracle-age-calculation-from-date-of-birth-and-today) — Robert Harvey, Dec 21 '19 at 20:19
@RobertHarvey Well I assumed my logic might be wrong and I could list users over 13 years old just using their date of birth but I didn't know how — Gentian Gashi, Dec 21 '19 at 20:29
What is the data-type of column _DateOfBirth_ ? And why do you need to `CAST` the columns _Username_, _FirstName_ and _LastName_ ? — Abra, Dec 21 '19 at 20:29
@Abra I had to cast the columns to make their character limits smaller so the columns would fit on screen, I was asked specifically not to change or alter the original sql script. Also I've included an image of how the table was created in my post. an example of a d.o.b : ```to_date('19-Apr-19','DD-MON-RR')``` — Gentian Gashi, Dec 21 '19 at 20:34
Please do not post images of code or data. Instead, please add them to your question as text - this makes it easier for people to reproduce your problem and find a solution. Thanks. — Bob Jarvis - Слава Україні, Dec 21 '19 at 20:40
Your query involves two database tables but it does not contain any kind of join. The tables `BR_USER` and `BR_APPCATEGORY` need to be somehow related. According to what you posted, I see no relation between them. — Abra, Dec 21 '19 at 20:49
@Abra I'm just initially trying to figure out how to list users above the age of 13 in my single BR_USER table. I'm not looking to join the tables as of yet. — Gentian Gashi, Dec 21 '19 at 20:52
@LukStorms it gives me an error ```ERROR at line 7: ORA-00904: "age": invalid identifier``` — Gentian Gashi, Dec 21 '19 at 20:52
@WilliamRobertson I'm not trying to take a substring, I'm trying to make columns smaller so they fit on screen while still displaying all the rows correctly. — Gentian Gashi, Dec 22 '19 at 00:01
@UnboundPhantom what screen? Most applications adjust result grids automatically. SQL\*Plus has column formatting commands. Using `cast` to avoid specifying a format like `col username format a20 trunc` just seems like overkill. — William Robertson, Dec 22 '19 at 10:14

score 2 · Answer 1 · answered Dec 21 '19 at 23:09

2

You should use direct date comparisons:

WHERE "DateOfBirth" < sysdate - interval '13' year

To be honest, this could cause a problem in leap years on Feb 29th. For some reason, Oracle allows this rare bug. So, add_months() is recommended:

WHERE "DateOfBirth" < add_months(sysdate, -13 * 12)

Note that both of these expressions are index-friendly, meaning that they can take advantage of an index on "DateOfBirth", if one is available and the optimizer thinks that is a good idea.

A function such as months_between() generally prevents an index from being used, which is why these solutions are preferable.

answered Dec 21 '19 at 23:09

Gordon Linoff

1,242,037
58
646
786

You are so competent at what you do. – Gentian Gashi Dec 21 '19 at 23:35
Regarding the buggy treatment of February, I believe this is mandated by ANSI. – William Robertson Dec 21 '19 at 23:57
@WilliamRobertson . . . If you have a reference I would appreciate it. I would note that Postgres, MySQL, and SQL Server (at least) all calculate one year before 2020-02-29 as 2019-02-28 (although the syntax varies among the databases). Subtracting or adding a year or month does not ever result in an error in these (and other) databases. – Gordon Linoff Dec 23 '19 at 03:25
@GordonLinoff it was a comment by Tom Kyte on [this Oracle-WTF blog post](https://oracle-wtf.blogspot.com/2006/02/stop-press-oracle-granted-license-to.html). (There seems to be something wrong with the Blogger code at the moment and some Twitter-related resource takes a minute to time out, so you have to wait a bit for the comments to appear, sorry.) – William Robertson Dec 23 '19 at 09:40
@GordonLinoff aha, page is slow in https. [Faster link here](http://oracle-wtf.blogspot.com/2006/02/stop-press-oracle-granted-license-to.html). – William Robertson Dec 23 '19 at 10:04

score 0 · Accepted Answer · answered Dec 21 '19 at 20:57

Using the linked answer in Robert Harvey's first comment to your question, plus the CREATE TABLE statement you provided, plus your clarification that at this stage you only want users over 13 years of age, the following SQL should provide you the results you desire.

SELECT substr(u."Username", 1, 20) AS "Username"
      ,substr(u."FirstName", 1, 15) AS "FirstName"
      ,substr(u."LastName", 1, 15) AS "LastName"
      ,TRUNC(u."DateOfBirth") AS "DateOfBirth"
 FROM BR_USER u
WHERE months_between(TRUNC(sysdate), u."DateOfBirth") / 12 > 13
ORDER BY u."DateOfBirth"

List Users Over a Certain Age

2 Answers2