Home / Questions / 299999
Accepted
RazerJs
Asked: 2020-10-11 13:44:54 +0800 CST 2020-10-11 13:44:54 +0800 CST

Function convert from year to century

  • 772

This sql query is to convert from a year to the century of this... what happens is that for cases that are years that are multiples of 100 it does not work for me since Eg: 1900 belongs to the last year of the 19th century, just like 2000 to the 20th century, etc... and my problem is that I don't know how to put the if conditions in sql in case the module yr/100=0does something, or else keep my yr/100+1...

select (yr/100+1) as century from years

enter image description here

This image is from my query for the years 1705, 1923, 1600, 2000, 444 and 88 respectively...

I am using PostgreSQL 9.6the table is years and the column yr

sql

1 Answers

  1. Best Answer

    Use directly date_part()indicating as a parameter century:

    SELECT date_part('century',TIMESTAMP '1900-01-01');

| date_part |
|-----------|
|        19 |

SELECT date_part('century',TIMESTAMP '1901-01-01');

| date_part |
|-----------|
|        20 |

    In case you only have one year as data, you could do:

    select (yr + 99) / 100
    • 5