April 18, 2012 at 12:18 pm
I am trying to save a timestamp to a Binary(8) column and converting it to Binary(8), with no luck updating the table where I am tring to save it. This is an example of what I am trying
// [LastBarcodeTimeStamp] is a Binary(8) datatype
UPDATE [TS].[dbo].[MPPS Data Vault]
SET [LastBarcodeTimeStamp] = (SELECT CONVERT(varbinary(max), MAX(timestamp), 1) as TS From [TS].[dbo].[Barcodes])
WHERE RowCounter = 1
This returns 1 record affected
******************************************************************************
If I run Just this:
SELECT CONVERT(varbinary(max), MAX(timestamp), 1) as TS From [TS].[dbo].[Barcodes]
it returns: 0x0000000016C1ADC3
As is should
Any ideas?
April 18, 2012 at 12:23 pm
Not enough information to really help.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ and follow the instructions on what information to post and how to post it. The more information you provide, the better answers you will get.
April 18, 2012 at 12:49 pm
Going along with Lynn, what is the data type of your timestamp column? Are you getting errors? If so, what errors? Can you run the SELECT CONVERT(...) statement by itself?
Jared
CE - Microsoft
April 18, 2012 at 12:57 pm
As I posted in the other thread. The timestamp datatype is deprecated. http://msdn.microsoft.com/en-us/library/ms182776%28v=sql.105%29.aspx
Use datetime to record datetime information.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 12:59 pm
Sean Lange (4/18/2012)
As I posted in the other thread. The timestamp datatype is deprecated. http://msdn.microsoft.com/en-us/library/ms182776%28v=sql.105%29.aspxUse datetime to record datetime information.
I think since it is used in MAX(), it is just a column name 🙂
Jared
CE - Microsoft
April 18, 2012 at 1:00 pm
SQLKnowItAll (4/18/2012)
Sean Lange (4/18/2012)
As I posted in the other thread. The timestamp datatype is deprecated. http://msdn.microsoft.com/en-us/library/ms182776%28v=sql.105%29.aspxUse datetime to record datetime information.
I think since it is used in MAX(), it is just a column name 🙂
That is what I am thinking as well. Hence the need for the datatypes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 18, 2012 at 1:05 pm
timestamp is the name of the field and it is a timestamp not null. I did not create the structure, and I am stuck with it.
April 18, 2012 at 1:06 pm
Sean Lange (4/18/2012)
As I posted in the other thread. The timestamp datatype is deprecated. http://msdn.microsoft.com/en-us/library/ms182776%28v=sql.105%29.aspxUse datetime to record datetime information.
If I read the link correctly, timestamp may be going away but I think its sister version, rowversion, will still be around. Please double check me on this as I seem to be getting a lot of things wrong today.
April 18, 2012 at 1:09 pm
Lynn Pettis (4/18/2012)
Sean Lange (4/18/2012)
As I posted in the other thread. The timestamp datatype is deprecated. http://msdn.microsoft.com/en-us/library/ms182776%28v=sql.105%29.aspxUse datetime to record datetime information.
If I read the link correctly, timestamp may be going away but I think its sister version, rowversion, will still be around. Please double check me on this as I seem to be getting a lot of things wrong today.
Yes that is the way I read that too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 1:10 pm
Have you read up on the timestamp data type? It is not "a timestamp," it is a data type called timestamp that is simply unique binary numbers. This has absolutely no relation to time as a measure.
Jared
CE - Microsoft
April 18, 2012 at 1:11 pm
Hmm... Also, why are you using a VARBINARY(MAX)? You do realize that will move all of that data off of the page?
Jared
CE - Microsoft
April 18, 2012 at 1:13 pm
ONE MORE! You still haven't told us what is not working... It is not updating? It is not updating correctly? What does "not correctly" mean? Don't make us fish, it is you who require help 🙂
Jared
CE - Microsoft
April 18, 2012 at 1:17 pm
More importantly what is the error message??? I bet it will tell you the problem.
"Cannot update a timestamp column" perhaps?
The timestamp (rowversion) datatype is NOT editable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 1:23 pm
[LastBarcodeTimeStamp] --I assume this column is a VARBINARY(MAX)?
CONVERT(varbinary(max), MAX(timestamp), 1) --Why MAX(timestamp)?
WHERE RowCounter = 1 --What data type is RowCounter column?
Jared
CE - Microsoft
April 18, 2012 at 1:28 pm
I think this should closely represent what you are trying to do?
create table #MyTable
(
RowTimeStamp rowversion,
SomeValue varchar(10) default 'asdf'
)
insert #MyTable default values
select * from #MyTable
create table #MyTable2
(
RowTimeStamp rowversion,
SomeValue varchar(10) default 'asdf'
)
insert #MyTable2 default values
select CAST(RowTimeStamp as BINARY(8)) from #MyTable
update #MyTable2 set RowTimeStamp = (select CAST(RowTimeStamp as BINARY(8)) from #MyTable)
select * from #MyTable2
drop table #MyTable
drop table #MyTable2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply