Convert yyyymmdd(int type) to date

Question

I need to use WEEK function so I need a date type.

While what I have in db is a int type like 20150101 representing 1st,Jan 2015.

I tried FROM_UNIXTIME but obviously it is not I want:

select FROM_UNIXTIME(20150101) from dual;
+-------------------------+
| FROM_UNIXTIME(20150101) |
+-------------------------+
| 1970-08-22 14:15:01     |
+-------------------------+

Do I have a way to convert the yyyymmdd(int) to a date type?

Answer 1

20150101 is not a unix timestamp its in the format ymd you need date_format to format it

mysql> select date_format('20150101','%Y-%m-%d') as date ;
+------------+
| date       |
+------------+
| 2015-01-01 |
+------------+
1 row in set (0.00 sec)


mysql> select unix_timestamp('2015-01-01');
+------------------------------+
| unix_timestamp('2015-01-01') |
+------------------------------+
|                   1420050600 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1420050600) ;
+---------------------------+
| FROM_UNIXTIME(1420050600) |
+---------------------------+
| 2015-01-01 00:00:00       |
+---------------------------+
1 row in set (0.00 sec)