• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

TimeStamp values appearing as NULL

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.

Scott

0
sneidig
Asked:
sneidig
  • 3
  • 2
  • 2
2 Solutions
 
obahatCommented:
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.

0
 
rafranciscoCommented:
You can use DATETIME and set the default value to GETDATE().
0
 
sneidigAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
obahatCommented:
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.

Omri.
0
 
sneidigAuthor Commented:
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.

Scott
0
 
sneidigAuthor Commented:
I'll add that obahat offers some interesting information about the binary datatype. Thank you.
0
 
rafranciscoCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now