HELP WITH TIMESTAMP CONVERT TO DATETIME

[CAPO ITURRIETA RAFAEL ANTONIO]

How convert timestamp to datetime ?
1.- select convert(datetime,convert(varbinary(8),getdate())) --> is ok

Jan 19 1994 14:03:23PM

2.-select convert(datetime,timestamp) --> is not ok

Jan 1 2019 12:00:00AM

Why ????? HELP????

[Teresa A Larson]

Because "timestamp" is a misnomer. Timestamp fields have nothing to do
with date or time information. If you do sp_help timestamp you'll
see that it's a Sybase-provided user-defined datatype that is really
varbinary(8). Also, here's an excerpt from a Sybase Technical Newsletter
about timestamps.

Timestamps Will Roll Over
-------------------------

Tech Support customers asked us, "Is the value of a timestamp
guaranteed to be monotonically increasing within a database?"
These customers wanted to use a timestamp field to identify
rows that have changed since the last time they looked, with a
where clause like where timestamp > @previous_timestamp.
The wanted just to add a timestamp field to their existing tables,
so that SQL Server would maintain the values, and they wouldn't have to
change any of their own code to identify newly inserted/updated rows.
(Deleted rows would need to be treated differently.)
The answer is that the timestamp is not guaranteed to increase
indefinitely. A timestamp is a 56-bit integer that will eventually roll
over, though it takes a very long time to do so. The only numbers that
won't roll over are ones with unbounded storage; there is no such
datatype in SQL Server.
However, Sybase does guarantee that if the row has changed, the
timestamp will differ from the one in the cached copy of the row.

Hope this helps
Teresa Larson

+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
| Teresa A. Larson - Hughes STX Corporation |
| NASA/GSFC Code 933.0 voice: (301) 286-7867 |
| Greenbelt, Maryland 20771 fax: (301) 286-1777 |
| Teresa...@gsfc.nasa.gov |
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
Standard disclaimer ...

[Craig Kim]

In article <D2oJq...@dcc.uchile.cl> rc...@cec.uchile.cl (CAPO ITURRIETA

The name 'timestamp' is the misnomer in that it does not represent the
time as we understand. It is a user-defined datatype, varbinary(8). It
is an approximate number of milliseconds the server has been up. There is
no direct correlation with the real world time.

--
Crai...@McCaw.COM -- CSPDBA
McCaw Cellular Communications, Inc. (206)803-7387
#include "the standard disclaimer"

[Tony Langdon]

You'll probably get a few follow-ups to this question, it is asked fairly often.

The datatype timestamp is a misnomer, it doesn't contain any time (or date)
information, and so cannot be converted to a datetime. Timestamp columns are
used for "browse" mode queries and are populated automatically by the server.

The FAQ gives a better explanation of this.

---
T.Langdon | Email : lan...@fidevf0.fi.gs.com
Goldman, Sachs & Co. | Phone : +1 212 357 6101
85 Broad Street |
New York, NY 10004 | Are "The Reds" still marching on?

[zbin...@gmail.com]

you can try this free online timestamp converter(http://www.online-code.net/unix-timestamp.html) to convert calendar date to timestamp.