May 15, 2002 at 3:07 pm
When I try the following sql
select a, convert(datetime,b) "timestamp" from tabletest
I get "1900-01-01 00:15:24.437" and
I got the "2002-05-15 13:37:07.640" when I executed
select getdate()
Could any one explain why I got the wrong datetime.
Thanks in advance
May 15, 2002 at 3:21 pm
What kind of datatype is b?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 3:25 pm
The Column b is timestamp datatype (nullable)
May 15, 2002 at 3:59 pm
Not sure what you mean? What's in tabletest? You should get the datetimes from the table. Getdate is the server date. Not related.
Steve Jones
May 15, 2002 at 5:48 pm
I just inserted data to tabletest which has column a int and b timestamp.
I wanted to display the timestamp field as a datetime data when I use convert the date i get is 1900....
Then I did the getdate to see the server datetime to cross check which is correctly set.
Can you tell me how to display a timestamp column in a datetime format?.
May 15, 2002 at 7:26 pm
From SQL BOL
quote:
timestampThe SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. Each time a page was modified, it was stamped with the current @@DBTS value and @@DBTS was incremented by one. This was sufficient for recovery to determine the relative sequence in which pages had been modified, but the timestamp values had no relationship to time.
In SQL Server version 7.0 and SQL Server 2000, @@DBTS is only incremented for use in timestamp columns. If a table contains a timestamp column, every time a row is modified by an INSERT, UPDATE, or DELETE statement, the timestamp value in the row is set to the current @@DBTS value, and then @@DBTS is incremented by one.
Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.
To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to automatically update the values when any modification takes place.
Just ahppens that the column timestamp you grabbed this time converted to that value, but the next whould be different and potentially extremely different.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 16, 2002 at 10:16 am
Thanks for the reply. I will change the timestamp column to datetime and write a trigger to track the modified time.
Thanks again
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply