w3resource

SQL Challenges-1: Century of a given date

SQL Challenges-1: Exercise-12 with Solution

From the following table, write a SQL query to find the century of a given date. Return the century.

Input:

Table: tablefortest

Structure:

FieldTypeNullKeyDefaultExtra
IDint(11)YES
date_of_birthdateYES

Data:

IDdate_of_birth
11907-08-15
21883-06-27
31900-01-01
41901-01-01
52005-09-01
61775-11-23
71800-01-01

Sample Solution:

SQL Code(MySQL):

DROP TABLE IF EXISTS tablefortest;
CREATE TABLE tablefortest(ID int,  date_of_birth date);
INSERT INTO tablefortest VALUES (1, '1907-08-15');
INSERT INTO tablefortest VALUES (2, '1883-06-27');
INSERT INTO tablefortest VALUES (3, '1900-01-01');
INSERT INTO tablefortest VALUES (4, '1901-01-01');
INSERT INTO tablefortest VALUES (5, '2005-09-01');
INSERT INTO tablefortest VALUES (6, '1775-11-23');
INSERT INTO tablefortest VALUES (7, '1800-01-01');
SELECT * FROM tablefortest;
SELECT id, date_of_birth, (SUBSTRING((EXTRACT(YEAR FROM(date_of_birth))-1),1,2))+1 AS Century 
FROM tablefortest;

Sample Output:

id|date_of_birth|Century|
--|-------------|-------|
 1|   1907-08-15|   20  |
 2|   1883-06-27|   19  |
 3|   1900-01-01|   19  |
 4|   1901-01-01|   20  |
 5|   2005-09-01|   21  |
 6|   1775-11-23|   18  |
 7|   1800-01-01|   18  |

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Convert negative numbers to positive and vice verse.
Next: Find the even or odd values.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8