October 14, 2004 at 4:11 pm
I am trying to use between dynamically to query a database to go one hour back from the current SQL Server time. Below is the query fragment. It did compile without error but didn't return any results. can you help?
where timestamp between DATEADD(hh,-1,GETDATE()) AND DATEADD(hh,0,GETDATE())
Thanks for your help
Festus Kahsay
October 16, 2004 at 9:16 pm
I would shorten the BETWEEN expression to:
DATEADD(hh,-1,GETDATE()) AND GETDATE()
But most importantly, what is the datatype of the column [timestamp]. If it is a 'timestamp' datatype, you will never get any results since the 'timestamp' datatype doesn't have anything to do with time.
Let us know the table schema to better help you.
October 18, 2004 at 8:15 am
Hi All,
drop table #temp
create table #temp
(
fldID int,
fldTstamp timestamp
)
insert into #temp (fldID) values(1)
insert into #temp (fldID) values(2)
insert into #temp (fldID) values(3)
insert into #temp (fldID) values(4)
insert into #temp (fldID) values(5)
insert into #temp (fldID) values(6)
insert into #temp (fldID) values(7)
insert into #temp (fldID) values(8)
insert into #temp (fldID) values(9)
insert into #temp (fldID) values(10)
insert into #temp (fldID) values(11)
insert into #temp (fldID) values(12)
select * from #temp
The result was
1 0x00000000000000A9
2 0x00000000000000AA
3 0x00000000000000AB
4 0x00000000000000AC
5 0x00000000000000AD
6 0x00000000000000AE
7 0x00000000000000AF
8 0x00000000000000B0
9 0x00000000000000B1
10 0x00000000000000B2
11 0x00000000000000B3
12 0x00000000000000B4
While looking at the result set, the timestamp values are produced in an order.
UPDATE #temp SET fldID = 3 WHERE fldID = 2
UPDATE #temp SET fldID = 4 WHERE fldID = 1
UPDATE #temp SET fldID = 5 WHERE fldID = 9
UPDATE #temp SET fldID = 7 WHERE fldID = 11
When the updated the table #temp
4 0x00000000000000B8
3 0x00000000000000B6
3 0x00000000000000AB
4 0x00000000000000AC
5 0x00000000000000AD
6 0x00000000000000AE
7 0x00000000000000AF
8 0x00000000000000B0
5 0x00000000000000B9
10 0x00000000000000B2
7 0x00000000000000BA
12 0x00000000000000B4
After updating the table #temp, you can see that only the updated rows timestamp values got changed, while other time stamp values remains intact.
By this we can find out which all rows got changed.
I don’t know whether we can incorporate BETWEEN keyword for timestamp columns.
With Regards,
Subu
October 19, 2004 at 8:38 pm
Again, the timestamp datatype has NOTHING to do with time. It is a serialized value that changes every time there is a change to any column in the row -and also it is NOT a sequential value for the row.
I believe it was renamed to 'rowversion' in SQL 2000.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply