Problem of casting to timestamp

Hello,
During my work I have found following problem. All tables that I am using
have column RecTimeStamp (timestamp). I do the selection based on last
remembered timestamp. For some reasons my last timestamp is saved as decimal
number (Int64), so I do some casting to timestamp in my queries. Once the D
B
global timestamp reaches 2147483647+1 i am at trouble. I will explain it by
following example:
select cast (cast (2147483648 as bigint) as timestamp)
result: 0x0000000080000000 - OK
select cast (2147483648 as timestamp)
result: 0x0A00000100000080 - MISSMATCH
select cast (2147483647 as timestamp)
result: 0x000000007FFFFFFF - OK
Is this a bug of MSSQL server?
Note: If I do no casting and simply use for example select top 1000 … wher
e
RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, hal
f
of them has timestamp < maximum value of smallint and half of them higher,
I’ll get no 1000 records but only that lower half of them.Michal Valenta,
Can you tell us the data type of that column?
SQL Server has timestamp data type but it has nothing to do with datetime
data type. it is used for row versioning.
select getdate(), current_timestamp, @.@.dbts;
AMB
"Michal Valenta" wrote:

> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as decim
al
> number (Int64), so I do some casting to timestamp in my queries. Once the
DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it b
y
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 … wh
ere
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB, h
alf
> of them has timestamp < maximum value of smallint and half of them higher,
> I’ll get no 1000 records but only that lower half of them.|||Alejandro Mesa,
The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
it has nothing to do with DateTime type. ;)
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Michal Valenta,
> Can you tell us the data type of that column?
> SQL Server has timestamp data type but it has nothing to do with datetime
> data type. it is used for row versioning.
> select getdate(), current_timestamp, @.@.dbts;
>
> AMB
> "Michal Valenta" wrote:
>|||Michal,
I have to admit that I am fogged as to why you want to convert timestamp to
anything. Timestamp is a nearly meaningless 8-byte incrementing number.
(The only meaning is that more recently changed rows have a higher timestamp
value than previously changed rows.)
Just in case you are expecting something different, remember that timestamp
was also called rowversion for a while (a better name) and contains no time.
Then I see your WHERE clause. Actually, you should never care how the
Binary(8) converts to compare with the interger value 0. Simply order by the
timestamp without a comparison, unless you allow the timestamp to be
nullable. If that is the case then, you may want:
WHERE RecTimeStamp IS NOT NULL
For your comparison to evaluate as you apparently intended:
WHERE RecTimeStamp > 0x00
RLF
"Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in message
news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
> Hello,
> During my work I have found following problem. All tables that I am using
> have column RecTimeStamp (timestamp). I do the selection based on last
> remembered timestamp. For some reasons my last timestamp is saved as
> decimal
> number (Int64), so I do some casting to timestamp in my queries. Once the
> DB
> global timestamp reaches 2147483647+1 i am at trouble. I will explain it
> by
> following example:
> select cast (cast (2147483648 as bigint) as timestamp)
> result: 0x0000000080000000 - OK
> select cast (2147483648 as timestamp)
> result: 0x0A00000100000080 - MISSMATCH
> select cast (2147483647 as timestamp)
> result: 0x000000007FFFFFFF - OK
> Is this a bug of MSSQL server?
> Note: If I do no casting and simply use for example select top 1000 .
> where
> RecTimeStamp > 0 order by RecTimeStamp and there are 1000 records in DB,
> half
> of them has timestamp < maximum value of smallint and half of them higher,
> I'll get no 1000 records but only that lower half of them.|||Michal Valenta,
See if this helps.
System.Data.SqlTypes Namespace
http://msdn.microsoft.com/library/d...atasqltypes.asp
AMB
"Michal Valenta" wrote:
[vbcol=seagreen]
> Alejandro Mesa,
> The datatype of RecTimeStamp column is exactly timestamp. Sure i know that
> it has nothing to do with DateTime type. ;)
> "Alejandro Mesa" wrote:
>|||Russell,
I have read the help for MSSQL and I've found a table which describes what
types are convertable and I am just doing something that might be possible.
The reason why I am using this column and its type is simple. As you wrote
about versioning. My clients are storing the last timestamp (version) from a
table as a decimal number Int64. They are asking server giving this version
mark if something new is present to download. The timestamp is not nullable
and contains DB-blobal number (i think binary(8)) incrementing by one. I am
selecting top 1000 records which has this mark higher then given and for nex
t
step (if return reaches 1000 records) I use the last downloaded timestamp.
This is functional system and i am using it more then three years for geting
all new and updated rows from DB. In the past I was always converting the
number by my C# code to timestamp format and pasting it to select. As I saw
the help, I started to use casting, that is simple. I know several steps how
to fix my problems and I already did, but what I do not understand is the
mechanism of casting using MSSQL, because it seems to be a bug in it. Try
those small selects as I wrote in example. So my problem and question is: is
that behaviour I described ok? I think it is not and I supose there is a bug
in MSSQL. Don't you think?
As you wrote about using > 0x00 you could have the same problems as i
described in a NOTE of my first posting.
Anyway thanks for trying to help, I am still hoping someone from MS could
know more about it... ;)
M.V.
"Russell Fields" wrote:

> Michal,
> I have to admit that I am fogged as to why you want to convert timestamp t
o
> anything. Timestamp is a nearly meaningless 8-byte incrementing number.
> (The only meaning is that more recently changed rows have a higher timesta
mp
> value than previously changed rows.)
> Just in case you are expecting something different, remember that timestam
p
> was also called rowversion for a while (a better name) and contains no tim
e.
> Then I see your WHERE clause. Actually, you should never care how the
> Binary(8) converts to compare with the interger value 0. Simply order by t
he
> timestamp without a comparison, unless you allow the timestamp to be
> nullable. If that is the case then, you may want:
> WHERE RecTimeStamp IS NOT NULL
> For your comparison to evaluate as you apparently intended:
> WHERE RecTimeStamp > 0x00
> RLF
>
> "Michal Valenta" <Michal Valenta@.discussions.microsoft.com> wrote in messa
ge
> news:0D53DDF0-7BFB-4662-9C50-8200F4FC0C0F@.microsoft.com...
>
>|||Alejandro,
thank you for trying to help, but this is not an answer that I am looking
for, for some more information please see my reply posting to Russell Fields
.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Michal Valenta,
> See if this helps.
> System.Data.SqlTypes Namespace
> http://msdn.microsoft.com/library/d...atasqltypes.asp
>
> AMB
>
> "Michal Valenta" wrote:
>|||Michal Valenta,
I have to admit that this is not the way I have used this feature in SQL
Server. I have used it when pulling rows to the client side, manipulating
them and pushing them back to the database but checking if the timestamp
column are the same. If the timestamp column changed at the database side,
then the data is not the latest. this is nothing else than using optimistic
concurrency.
I think that datetime data type could fit better for what you are trying to
accomplish, because you are not comparing row based, instead you are
comparing set based.
AMB
"Michal Valenta" wrote:
[vbcol=seagreen]
> Russell,
> I have read the help for MSSQL and I've found a table which describes what
> types are convertable and I am just doing something that might be possible
.
> The reason why I am using this column and its type is simple. As you wrote
> about versioning. My clients are storing the last timestamp (version) from
a
> table as a decimal number Int64. They are asking server giving this versio
n
> mark if something new is present to download. The timestamp is not nullabl
e
> and contains DB-blobal number (i think binary(8)) incrementing by one. I a
m
> selecting top 1000 records which has this mark higher then given and for n
ext
> step (if return reaches 1000 records) I use the last downloaded timestamp.
> This is functional system and i am using it more then three years for geti
ng
> all new and updated rows from DB. In the past I was always converting the
> number by my C# code to timestamp format and pasting it to select. As I sa
w
> the help, I started to use casting, that is simple. I know several steps h
ow
> to fix my problems and I already did, but what I do not understand is the
> mechanism of casting using MSSQL, because it seems to be a bug in it. Try
> those small selects as I wrote in example. So my problem and question is:
is
> that behaviour I described ok? I think it is not and I supose there is a b
ug
> in MSSQL. Don't you think?
> As you wrote about using > 0x00 you could have the same problems as i
> described in a NOTE of my first posting.
> Anyway thanks for trying to help, I am still hoping someone from MS could
> know more about it... ;)
> M.V.
> "Russell Fields" wrote:
>