TimeStamp values appearing as NULL

Posted on 2005-04-20
Last Modified: 2010-03-19
Hello and thanks for taking time to read this.

I've got a table with 1500 rows in it. With enterprise manager I added a [TimeStamp] column. The values for this column all appeared as NULL. I've tried making an update to the rows to get the TimeStamp value to change from null to an actual timestamp, but they stay NULL. Also, If I insert a new row the value for that row's timestamp is NULL.

Apparently I don't understand how TimeStamp works. Can someone please clue me on what's going on? Thanks for your time.


Question by:sneidig
    LVL 5

    Expert Comment

    Timestamp is a binary column that is typically used for various internal SQL server needs. If you need to capture the date and time, please use DATETIME OR SMALLDATETIME data type instead.

    You can learn more about TIMESTAMP variables in books online (simply look at Query Analyzer help, under timestamp).
    Basically, the value in the timestamp column is (typically) assigned automatically by SQL server.

    LVL 28

    Assisted Solution

    You can use DATETIME and set the default value to GETDATE().

    Author Comment

    Thanks, for answering, I do actually need the TimeStamp column for a reason. It's to implement incremental full-text indexing.

    I figured out a way to address part of the problem by exporting the data, then deleting all the rows, and then importing the data. That creates not null values for that column in each row.

    However when I go to insert a new row I get this message in query analyzer:
    Cannot insert the value NULL into column 'Timestamp', table 'Adobe_Photographers_Directory.dbo.pd_photographers'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    That's where I'm at so far.
    LVL 5

    Accepted Solution

    The values are displayed as NULL since binary data is not displayable by means of (n)varchar or (n)char characters.
    So, even if there are characters in the field the display may say NULL.

    Now, in order to actually populate the table, you can do so programmatically.
    For example:

    declare @a timestamp
    set @a = 0x014
    select @a

    you can later write @a into the table (through UPDATE/INSERT, and such).

    Hope this helps.


    Author Comment

    Thanks for your help guys. It turns out that between me and the person I'm working on this, we didn't add the column correctly in enterprise manager. (i'm embarresed and blushing if you can't see). Instead of just typing in the name [TimeStamp] in design view, you also have to select TimeStamp as the datatype from the drop down menu. It's a little confusing because as soon as you name the column [TimeStamp], the datatype automaticly gets set to binary, so we assumed that was the correct datatype.

    I'm going to split up the points since you guys rushed to help. Also, I just have to say RaFrancisco is THE MAN or WOMAN on this web site.


    Author Comment

    I'll add that obahat offers some interesting information about the binary datatype. Thank you.
    LVL 28

    Expert Comment

    sneidig, thanks for the compliments.  There are a lot of great experts in this Topic Area and I am just playing catch up with them.  Glad that I was able to help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now