Timestamp to Datetime

Question

Timestamp to Datetime

leo4Ever

Right there with Babe

Points: 735
More actions
April 6, 2009 at 4:08 pm

#384301

Dear SQL experts,
I am encountering an "Conversion failed when converting datetime from binary/varbinary string" when running the following test sql.
select CONVERT(datetime, 0x00000000018B8200, 200)
However, if I change the hex value to be 0x00000000018B8199 it works.
After some researching I found that, timestamp gets uniquely generated every time a record gets insert or updated. I have been using the timestamp column in my table to compare against row changes. Now, I believe the hex value has passed a certain value (0x00000000018B8199) which cannot be parsed into a datetime value anymore.
I am wondering if there is any other workaround or possible solution to get this issue fixed. Is it possible to reset the timestamp versioning value?
Thanks for any help.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

I wasn't aware that the timestamp could be parsed into datetime. It's supposed to be a versioning value, not related to when something occurred. It's updated with every change to the database.

What would you reset it to? Are you trying to determine when some event took place?

leo4Ever Right there with Babe Points: 735 More actions · Answer 2

Actually I am working on some stored procedures that was written by another developer. In my case, the table has a timestamp column, which gets cast to a datetime value and is sent to the UI, when the UI comes back with the changes, this datetime value is compared before committing the transaction.

For ex:

SELECT ID, CAST (TIMESTAMP_COLUMN AS DATETIME) AS STAMP FROM MyTable

In the update sproc, the STAMP values is passed as a parameter, so before updating the fields, a comparison is done

IF @STAMP = (SELECT CAST(TIMESTAMP_COLUMN) AS DATETIME FROM MyTable)

update the data

ELSE

Throw an error, as the data is stale.

Hope, the above logic makes sense to you.

So, the error I am getting is the timestamp_column value has passed some certain HEX value which I am unable to cast as datetime anymore, it gives me cannot convert error.

So, I was wondering if its somehow possible to reset the timestamp value generation, so it starts from the beginning which I would be able to cast as datetime.

Though, I understand that timestamp shouldn't be casted as datetime, I am unable to change this as it would require lot of changes in the UI and several stored procedures.

Thanks,

Javid

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

You cannot reset the timestamp generation.

What you are describing is bug that needs to be fixed. There was never any reason to assume that a timestamp column can be converted to a datetime.

You can just say you were lucky until now, or unlucky because this did not fail during initial testing.

leo4Ever Right there with Babe Points: 735 More actions · Answer 4

True,

I completely agree with you that it is a bug. I was just looking for a quick workaround to keep the process going until I fix it with a elegant solution.

Also, would you be able to point me to some sample code that I could use for row versioning?

Thanks,

Javid

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

javid.alimohideen (4/6/2009)
Actually I am working on some stored procedures that was written by another developer. In my case, the table has a timestamp column, which gets cast to a datetime value and is sent to the UI, when the UI comes back with the changes, this datetime value is compared before committing the transaction.
For ex:
SELECT ID, CAST (TIMESTAMP_COLUMN AS DATETIME) AS STAMP FROM MyTable
In the update sproc, the STAMP values is passed as a parameter, so before updating the fields, a comparison is done
IF @STAMP = (SELECT CAST(TIMESTAMP_COLUMN) AS DATETIME FROM MyTable)
update the data
ELSE
Throw an error, as the data is stale.
Hope, the above logic makes sense to you.
So, the error I am getting is the timestamp_column value has passed some certain HEX value which I am unable to cast as datetime anymore, it gives me cannot convert error.
So, I was wondering if its somehow possible to reset the timestamp value generation, so it starts from the beginning which I would be able to cast as datetime.
Though, I understand that timestamp shouldn't be casted as datetime, I am unable to change this as it would require lot of changes in the UI and several stored procedures.
Thanks,
Javid

TimeStamp data types have absolutely nothing to do with dates or times. They been grossly misnamed because all they do is carry a database wide unique number. Whatever is appearing on the screen as a date time is either wrong or had gone through some database wide translation table and I think the latter of the two not likely.

Someone really needs to look into the app because this is just wrong. And, please don't kill the messenger... I know it would take a lot of time to fix, but a fix is going to be absolutely necessary.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

Ah... sorry... bad day and I didn't scroll down. I see that folks have already explained that and you agree.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jerry Hung SSChampion Points: 12968 More actions · Answer 7

another reason why Microsoft should rename the TIMESTAMPE data type

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 8

Heh... actually, I love it being there... I'm currently working as a consultant and gives me more to fix. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 9

javid.alimohideen (4/6/2009)
True,
I completely agree with you that it is a bug. I was just looking for a quick workaround to keep the process going until I fix it with a elegant solution.
Also, would you be able to point me to some sample code that I could use for row versioning?
Thanks,
Javid

I guess the question would be, at this point, do you want row versioning or just dates that tell you when a row was last updated or... both?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

leo4Ever Right there with Babe Points: 735 More actions · Answer 10

Thats good as long as you know how to fix it. I have been breaking my head in the past couple of days to actually come up with the right fix. I would very much appreciate if you could give me some pointers.

Thanks,

Javid

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 11

Jerry Hung (4/7/2009)
another reason why Microsoft should rename the TIMESTAMPE data type

... another reason why the name "TIMESTAMP" should be removed:

In ODBC the SQL Server DATETIME is mapped to type "TIMESTAMP"; the SQL Server "TIMESTAMP" is mapped to BINARY. Would appreciate to get one dollar for every time somebody mixed them here...

Greets

Flo

leo4Ever Right there with Babe Points: 735 More actions · Answer 12

I would prefer to have a row versioning. I believe if I start using dates, then I might have to add some additional triggers that needs to be executed as part of some updates are done by automated processes and parts of it from the UI. Since my tables are already using the timestamp column, I was think to keep it the same way and start modifying the stored procedures to not cast the timestamp to datetime. I am currently figuring out how to handle the timestamp data type in C# ASP.Net code. I think the datatype SqlDbType.Binary should work for my case.

If you could suggest any other solution, that you feel is the right way to do it, its very welcomed.

Thanks,

Javid

leo4Ever Right there with Babe Points: 735 More actions · Answer 13

Ok, I managed to fix the issue. Though this appears to be a quick workaround, I believe this will work long term (atleast for next 100 years in my case)

CONVERT(DATETIME, CONVERT(BIGINT, TIMESTAMPVALUE) * 0.0000001) AS STAMP

I know the real sql server people, would not agree with me on doing something like this.

The above code could fail when the timestamp value reaches 2**45

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 14

EDIT: Sorry this previous posting was wrong on a couple of aspects. I've fixed it up below.

The timestamp data type aka rowversion (which is a much better name for it), is equivalent to a binary(8) datatype.

The datetime data type uses two 4-byte integers, but not the entire range of each 4-byte integer is valid. Therefore, there is the potential for data loss when converting from a timestamp/rowversion to a datetime. The first 4-byte integer stores the date and the valid range is -53690 (1753-01-01) to 2958463 (9999-12-31), making a total of 3012154 discrete values. The second 4-byte integer stores the time and the valid range is 0 to 25919999 (1/300 second intervals in a day), making 25920000 discrete values. Therefore, the maximum number of discrete values that a datetime value can store is 3012154 * 25920000 = 78075031680000.

Therefore if you must do this conversion as a temporary workaround, then the following will use the full valid range of a datetime and it will also cycle back to the minimum datetime value if the timestamp/rowversion value (when converted to a bigint) reaches 78075031680000.

DECLARE @ts binary(8)

SET @ts = 0x00004702428773FF

DECLARE @b-2 bigint

SET @b-2 = CONVERT(bigint, @ts) % 78075031680000

SELECT CONVERT(datetime, CONVERT(binary(4), @b-2 / 25920000 - 53690) + CONVERT(binary(4), @b-2 % 25920000))

Obviously, the actual value of the resulting datetime column is meaningless, and I agree with Jeff that the problem needs to be fixed properly asap.