[Last Call] Learn how to a build a cloud-first strategyRegister Now


TimeStamp values appearing as NULL

Posted on 2005-04-20
Medium Priority
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
  • 3
  • 2
  • 2

Expert Comment

ID: 13828251
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

rafrancisco earned 800 total points
ID: 13828309
You can use DATETIME and set the default value to GETDATE().

Author Comment

ID: 13828354
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

obahat earned 1200 total points
ID: 13828393
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

ID: 13828449
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

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

Expert Comment

ID: 13829455
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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

834 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