How to convert a SQL field stored as a tick into a date

Question

I am exporting info from a database that has field with a birthdate stored as a tick. I need to convert this to a regular date. I can either do it in the SQL statement (if there is a way?) or convert it into excel since I will import my data there?

EDIT: Sorry, not very familiar with ticks, so wasn't sure what info to include. The database is postgreSQL. Just putting it in a format mm/dd/yyyy is fine (excel should understand that).

'tick' is pretty non-specific. How fast does your click tick, and what's its epoch? — Marc B, Jan 29 '12 at 19:52
possible duplicate of [Convert .NET Ticks to SQL Server DateTime](http://stackoverflow.com/questions/2313236/convert-net-ticks-to-sql-server-datetime) — Magnus, Jan 29 '12 at 19:57

Wize · Answer 1 · 2017-11-17T11:04:51.850

3

You need to convert the .NET ticks to UNIX Epoch and then to a postgreSQL timestamp.

to_timestamp((("date" - 621355968000000000) / 10000000))

This code will work with version 9 and above

edited Nov 17 '17 at 11:04

answered Nov 11 '14 at 12:12

Wize

1,040
9
20

This expression loose milliseconds. Compare: `SELECT to_timestamp(((636892826050036451 - 621355968000000000) / 10000000.0)) as with_milliseconds, to_timestamp(((636892826050036451 - 621355968000000000) / 10000000)) as without_milliseconds` – Maxim Apr 23 '19 at 12:05

How to convert a SQL field stored as a tick into a date

1 Answers1