Interpreting Dates from SQL Query results

Question

So I am querying a particular table from a SQL DB. The table contains few columns that are related to date. When I use a Select query on the table, the results are shown like below where some Alias/random numbers are shown instead of the actual dates ( F_CRTM and F_EDTM are meant to be columns that show the actual dates ). Can someone help me on how to get the actual value that I am looking for( in this case date). I tried to see the table structure, but unfortuately I dont have access to that. So, I dont have any idea whether this is because of the way the table itself is structured or if there is any I can do about it.

The results that I get are shown below:

 F_SGRP          F_CRTM          F_EDTM
    1452759805  1452759805       1452759805
    1452759946  1452759946       1452759946
    1452759975  1452759975       1452759975
    1452760148  1452760148       1452761593
    1452760411  1452760411       1452761580
    1452761896  1452761896       1452761896
    1452761903  1452761903       1452761903

Are you using MySQL or MS SQL Server? (When it comes do date/time, those products are not compatible.) — jarlh, Jun 12 '19 at 09:15
See https://stackoverflow.com/questions/8837225/unix-timestamp-in-sql-server — Joakim Danielson, Jun 12 '19 at 09:24

score 0 · Accepted Answer · answered Jun 12 '19 at 11:13

Just use date arithmetic:

select date_add(second, F_SGRP, '1970-01-01') as F_SGRP_DATETIME,
       date_add(second, F_CRTM, '1970-01-01') as F_CRTM_DATETIME,
       date_add(second, F_EDTM, '1970-01-01') as F_EDTM_DATETIME
from t;

These look like Unix style datetime formats, so the value is the number of seconds starting on 1970-01-01.

score -1 · Answer 2 · answered Jun 12 '19 at 09:07

-1

OK,just use from_unixtime to implement your requirement, the examples as below:

mysql> select from_unixtime(1452759805) as time;
+---------------------+
| time                |
+---------------------+
| 2016-01-14 16:23:25 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date(from_unixtime(1452759805)) as date;
+------------+
| date       |
+------------+
| 2016-01-14 |
+------------+
1 row in set (0.00 sec)

So your complete SQL maybe:

select
    date(from_unixtime(F_SGRP)) as F_SGRP_DATE,
    date(from_unixtime(F_CRTM)) as F_CRTM_DATE,
    date(from_unixtime(F_EDTM)) as F_EDTM_DATE
from
    your_table

answered Jun 12 '19 at 09:07

Shawn.X

1,323
6
15

Thanks a lot for this. But this looks like a function that wont work in SQL 2008 (as the DB I am working on is 2008 ) because I am getting an error that 'from_unixtime' is not a recognized built-in function name. Is there a way to achieve this in SQL 2008? – Krishna Jun 12 '19 at 09:17
@Krishna OK, you write `mysql` tag in your question, so I thought you just use a mysql database now. :D – Shawn.X Jun 12 '19 at 09:21
https://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-format/8119407 this maybe your answer in a `sql server` way. @Krishna – Shawn.X Jun 12 '19 at 09:22
Apologies for that tag :( Thank you one again. Let me have a look at it and try :) – Krishna Jun 12 '19 at 09:23

Interpreting Dates from SQL Query results

2 Answers2