SQL Challenges-1: Century of a given date

Last update on December 20 2024 11:58:40 (UTC/GMT +8 hours)

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

Input:

Table: tablefortest

Structure:

Data:

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.