CODEWITHSUNDEEP
sqlsqlitedatedatetime
erikci
erikci

Reputation: 159

how to separate year from MM/DD/YY format on SQLite

I have table with a dates formated as MM/DD/YY but it can also be like M/D/YY or MM/D/YY or M/DD/YY so I can't use substr since lengt of string changes..

For example 1/2/98 or 10/5/12 or 12/2/10 etc. etc. Since SQlite dont have any to_date function I can't use that either.

Table looks like this;

+-------+----------+
| NAME  |   DATE   |
+-------+----------+
| John  | 12/1/04  |
| Megan | 4/11/85  |
| Doe   | 9/18/17  |
| Smith | 10/27/10 |
+-------+----------+

And the expected output is this;

+-------+------+
| NAME  | DATE |
+-------+------+
| John  |   04 |
| Megan |   85 |
| Doe   |   17 |
| Smith |   10 |
+-------+------+

and I don't know how but if crazy coder can make this like

+-------+------+
| NAME  | DATE |
+-------+------+
| John  | 2004 |
| Megan | 1985 |
| Doe   | 2017 |
| Smith | 2010 |
+-------+------+

that would be so so awesome. Possible constraints are that anything <20 is 2000's and >20 is 1900's

Upvotes: 1

Views: 473

Answers (1)

GMB
GMB

Reputation: 222482

You can use string functions.

This gives you the first resultset:

select name, substr(date, -2) date from mytable

The second resulset requires a little conditional logic:

select 
    name, 
    1900 + substr(date, -2) + 100 * (0 + substr(date, -2) <= 20) date
from mytable

It might be easier to understand with a case statement:

select 
    name, 
    case when 0 + substr(date, -2) <= 20 
        then 2000 + substr(date, -2) 
        else 1900 + substr(date, -2) 
    end date
from mytable

Demo on DB Fiddle:

name  | date
:---- | ---:
John  | 2004
Megan | 1985
Doe   | 2017
Smith | 2010

Upvotes: 4

Related Questions