CODEWITHSUNDEEP
sqloracle-database
Gentian Gashi
Gentian Gashi

Reputation: 341

List Users Over a Certain Age

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
)
/

Upvotes: 0

Views: 974

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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.

Upvotes: 2

Abra
Abra

Reputation: 20914

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"

Upvotes: 0

Related Questions