Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Timestamp datatype - Does it store Date or Time?

Dec 26 2011 1:47AM by Madhivanan   

Often deveopers confuse themselves between Timestamp and Datetime datatypes. But they are entirely different datatypes. Although the name has Time, the Timestamp datatype has nothing to do anything with date or time. Also you cannot explicitely add/update that column. It gets updated whenever data are added or updated in the table.

Consider the following example

declare @t table(names varchar(100), uid timestamp)
insert into @t (names) 
select 'test1' union all
select 'test2'

select * from @t 

Which produces the following result

names                    uid
------------------------ ------------------
test1                    0x00000000000007D8
test2                    0x00000000000007D9

Now update the first record and see the result

update @t 
set names='test100' where names='test1'

select * from @t 

names                    uid
------------------------ ------------------
test1                    0x00000000000007DA
test2                    0x00000000000007D9

As you see values of Timestamp column is automatically updated whenever data are added or modified. So do not confuse with Datetime datatype. Timestamp datatype does not represent any date ot time values.

Tags: t-sql, sql_server, sqlserver, tsql, SQL Server, #SQLServer, timestamp,


Madhivanan
3 · 40% · 12958
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

4  Comments  

  • This is the reason why timestamp columns are quite often used for row versioning/incremental data pulls.

    commented on Dec 31 2011 6:08AM
    Dattatrey Sindol (Datta)
    42 · 4% · 1336
  • Also, you can not insert explicit value in the TIMESTAMP column, SQL will throw an error --

    declare @t table(names varchar(100), uid timestamp)
    insert into @t (names, uid) 
    select 'test1', 1 union all
    select 'test2', 1
    
    select * from @t
    

    Msg 273, Level 16, State 1, Line 2 Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

    But, you can use NULL instead of 1.

    declare @t table(names varchar(100), uid timestamp)
    insert into @t (names, uid) 
    select 'test1', null union all
    select 'test2', null
    
    select * from @t
    

    Result is:

    names   uid
    test1   0x00000000000007DE
    test2   0x00000000000007DF
    
    commented on Jan 6 2012 1:41AM
    Hardik Doshi
    20 · 9% · 2864
  • @Datta: Be careful - TIMESTAMP is actually marked for deprecation - the replacement is the ROWVERSION syntax: http://msdn.microsoft.com/en-us/library/ms182776.aspx

    commented on Jan 17 2012 1:17PM
    Nakul Vachhrajani
    4 · 36% · 11645
  • and to get the current timestamp value use @@dbts

    commented on Jan 17 2012 3:26PM
    Jeetendra
    145 · 1% · 342

Your Comment


Sign Up or Login to post a comment.

"Timestamp datatype - Does it store Date or Time?" rated 5 out of 5 by 4 readers
Timestamp datatype - Does it store Date or Time? , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]