TimeStamp in Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

TimeStamp in Dynamic SQL

Author

Topic

Sprinjee
Starting Member

42 Posts

Posted - 2005-08-26 : 08:01:24

I can't seem to get this working can anyone help me out:

Server: Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type varchar to timestamp is not allowed. Use the CONVERT function to run this query.
Server: Msg 403, Level 16, State 1, Line 4
Invalid operator for data type. Operator equals add, type equals timestamp.

Query:

declare @FTS timestamp, @CTS timestamp, @sql varchar(1000)
set @FTS = (select top 1 FTSP from first_timestamp)
set @CTS = (select top 1 CTSP from cur_timestamp)
set @MyTable = 'variable'
set @sql = 'select * from ' + @MyTable + ' where TimeStamp between ' + @FTS + ' and ' + @CTS
exec (@sql)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-26 : 08:09:25

This is bad practice to dynamically select the table... but anyway...

declare @FTS timestamp, @CTS timestamp, @sql varchar(1000), @myTable varchar(100)
set @FTS = @@DBTS
set @CTS = @@DBTS
set @MyTable = 'variable'
set @sql = 'select * from ' + @MyTable + ' where TimeStamp between ' + convert(varchar,@FTS) + ' and ' + convert(varchar,@CTS)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Sprinjee
Starting Member

42 Posts

Posted - 2005-09-12 : 07:58:31

while executing this statement I get:

The name 'select * from MyTable where TimeStamp between .....k¬ and .....k¬' is not a valid identifier

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-12 : 09:11:46

i don't think you can do it like this.
you'll probably need sp_executeSQL for this.

Go with the flow & have fun! Else fight the flow

KLang23
Posting Yak Master

115 Posts

Posted - 2005-09-12 : 17:15:26

HI,

I think you need to wrap another set of quoted around the timestamp values:


declare @FTS timestamp, @CTS timestamp, @sql varchar(1000), @myTable varchar(100)
set @FTS = @@DBTS
set @CTS = @@DBTS
set @MyTable = 'variable'
set @sql = 'select * from ' + @MyTable + ' where TimeStamp between ''' + convert(varchar,@FTS) + ''' and '''' + convert(varchar,@CTS)   + ''''

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-12 : 17:28:37

Why are you trying to select by a timestamp column? Do you know that a timestamp column has nothing to do with time? It is actually a Rowversion, and the name has been changed to that in SQL 2000. It you are using it for a range of inserted data, there is nothing in the documentation that says it is sequential.

CODO ERGO SUM

Sprinjee
Starting Member

42 Posts

Posted - 2005-09-13 : 11:34:34

Thanks for your reply. I wonder why it is then that if I do a:

select top 1 * from mytable order by timestamp desc

it does present the last inserted row, not tested very thouroughly but judging from your last post I must conclude that this is just coincidence?

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-13 : 12:08:19

Are you coming over from DB2?

I don't think you mean TimeStamp..or is that really the name of the column?

Follow the hint link below....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-13 : 12:12:50

quote:
Originally posted by Michael Valentine Jones

Why are you trying to select by a timestamp column? Do you know that a timestamp column has nothing to do with time?

Well that's what I meant, nut in DB2, it is equivalent to datetime + an extra 3 millisecond decimal places.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2005-09-13 : 12:56:26

"I don't think you mean TimeStamp..or is that really the name of the column?"

I looked at that too, and then had a faint recollection that SQL Server can automatically assign a column name of "timestamp" to the [single] column that contains the timestamp datatype - however I can't remember what the circumstances are when it does that - but I'm sure you'll know what I'm blabbering about Brett!

Kristen

Sprinjee
Starting Member

42 Posts

Posted - 2005-09-19 : 15:23:56

No special circumstances as far as I can see. Creating the column with the datatype is enough. Doing an insert sets a puts a timestamp in the column. I looked on several sites and timestamp is

- unique
- autoincrement

I think it therefore suits my purpose. I want to see recent update inside a particular table.

By the way I still get "not invalid identifier" even with the extra quotes...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-19 : 16:33:11

Cast the column to bigint, and have tha variables as bigint (or cast them).
bigint = 8 bytes, timestamp = 8 bytes.

Sprinjee
Starting Member

42 Posts

Posted - 2005-09-19 : 17:23:35

You mean like this:

declare @FTS timestamp, @CTS timestamp, @sql varchar(1000), @myTable varchar(100)
set @FTS = @@DBTS
set @CTS = @@DBTS
set @MyTable = 'gbkmut'
set @sql = 'select * from ' + @MyTable + ' where cast(TimeStamp as bigint(8)) between ' + cast(@FTS as bigint(8)) + ' and ' + cast(@CTS as bigint(8)) + ')'
exec @sql

(this doesn't work: Error converting data type varchar to bigint.)

Sprinjee
Starting Member

42 Posts

Posted - 2005-09-19 : 17:28:23

Never mind...I put in a workaround replace the timestamp variables by select statement as in (select top 1 timestamp from table_with_timestamp). Since it loops around several tables and columns it just takes a little while longer for the query to execute. The result is there though

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-19 : 17:45:46

~~bigint(8)~~
bigint

I don't understand what you are doing.

sql is somewhat typed, you cant do:
'blabla' + 123
i.e.
character data + numeric data

there are some imlicit casts, but you still have to know some programming basics.

rheitzman
Starting Member

16 Posts

Posted - 2005-09-21 : 20:21:28

You can only reliably use timestamp to compare a local copy of a record to the DB version. It isn't really a time stamp.

From help:
timestamp
The 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources