Renier Schutte Renier Schutte - 1 year ago 70
SQL Question

How do I convert this value into a datetime value I can understand in SQL

I am trying to write report in SQL for a ticket database and i need to filter on the date and time.

Unfortunately the values in the date time fields dont make sense to me.

How can I convert

to a

Here are the strings and the dates and time that correspond to them.

1473140017153 06/09/2016 07:33 AM
1473140228660 06/09/2016 07:37 AM

Answer Source

We have no clue what RDBMS you are using, forcing us to basically give you answers for all possibilities:

SQL Server:

DATEADD(ms, col, '19700101')


DATEADD(s, col / 1000, '19700101')


TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(col / 1000, 'SECOND')


FROM_UNIXTIME(col / 1000)


TO_TIMESTAMP(col / 1000)

If you're using SQLite, you're out of luck :-)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download