--- title: "Misunderstanding timestamp" url: "https://osherove.com/blog/2004/4/15/misunderstanding-timestamp.md" canonical: "https://osherove.com/blog/2004/4/15/misunderstanding-timestamp.html" published: "2004-04-15T14:21:00" updated: "2004-04-15T14:21:00" tags: [".Net Original"] author: "Roy Osherove" description: "timestamp columns in SQL server are misunderstood. I know they are because today I was definitely misunderstanding them, and I'm (according to Google) not the first. So what's to not understand? Well, here's the deal. I…" usage: "Licensed under CC BY 4.0 — reuse, quote and adapt with attribution (Roy Osherove, link to the original page)." license: "https://creativecommons.org/licenses/by/4.0/" --- # Misunderstanding timestamp By Roy Osherove April 15, 2004 In [.Net Original](https://osherove.com/blog/category/net-original) **Historical article** · Originally published April 15, 2004; technical details may be outdated. timestamp columns in SQL server are misunderstood. I know they are because today I was definitely misunderstanding them, and I'm (according to Google) not the first. So what's to not understand? Well, here's the deal. I had an application that required me to log for each row in a specific table the exact time at which it was last updated/Inserted. “Great!” I thought, “Sounds like a task for the timestamp column!” . And so we tried. The funny thing about a timestamp column is that when we wanted to retrieve it from the database in the unit test, we had to Cast it into a datetime DB type, since it is Binary. That should have been my first clue, but I kept on. When we finally got the data back, the year of the date column was always set to 1900. Yep. More inserts, more 1900. Each row was different value in a way, waay back at the end of the number, but the date was absolutely not what I expected. So what was the problem? The problem was that we were using timestamp for something it wasn't made for. A timestamp is there simply for the purpose of keeping track on the “version” of one unique row in a database table. It is a totally binary number that changes, but it does **not** represent a datetime construct. The only thing promised is that whenever a row is inserted/updated, that the value in that column will stay unique across changes of the specific row. that's it. Since it was **not** a datetime construct, casting it into a datetime type resulted in how the datetime casting engine parsed this binary values, defaulting to 1900 because there really was no year specified in the converted value. So now that its established that we do **not** want to use a timestamp, how do I keep track of a row's “last updated” state? The hard way, that's how. I can either get the date as part of a stored procedure parameter and set it every update, or I can just set it on updates and inserts automatically inside the stored procedure using “GETDATE()” functionality in SQL. There's another way: doing a trigger on Update and Insert to set the column value to the current date, but triggers are something I try to avoid, so let's ignore that possibility. ## More in .Net Original [All posts in .Net Original](https://osherove.com/blog/category/net-original) - Apr 21, 2004 ### [Ack! My DataTable magically grew a new data column. Here's why.](https://osherove.com/blog/2004/4/21/ack-my-datatable-magically-grew-a-new-data-column-heres-why.html) - Apr 20, 2004 ### [The Regulator v. 2.01 - now with Intellisense](https://osherove.com/blog/2004/4/20/the-regulator-v-201-now-with-intellisense.html) - Apr 14, 2004 ### [Connection pools and a DAAB v. 3 mystery](https://osherove.com/blog/2004/4/14/connection-pools-and-a-daab-v-3-mystery.html) - Apr 11, 2004 ### [DotNet vs. Smalltalk](https://osherove.com/blog/2004/4/11/dotnet-vs-smalltalk.html) --- ## About the author **Roy Osherove**: Senior Solutions Architect at AWS working on AI for production and AI-driven development; author of The Art of Unit Testing and Elastic Leadership, with about 20 years of consulting and training on unit testing, TDD, team leadership and continuous delivery. ### Sites - [RobotPaper](https://robotpaper.ai/): This blog: AI patterns and anti-patterns, AI and coding agents, and engineering leadership. - [osherove.com](https://osherove.com): Roy's personal site. Author of The Art of Unit Testing and Elastic Leadership; consulting and training on unit testing, TDD, team leadership and continuous delivery, plus about 20 years of blog posts. - [The Art of Unit Testing](https://www.artofunittesting.com): The Art of Unit Testing, 3rd Edition (Manning): readable, maintainable, trustworthy unit tests, plus TDD workshops in C#, JavaScript, Java and Kotlin. - [5whys.com](https://www.5whys.com): Roy's writing on software team leadership: self-organizing teams, surviving chaos, learning mode and the Elastic Leadership model. - [Pipeline Driven](https://pipelinedriven.org): Continuous delivery and DevOps practices: build patterns, pipeline-friendly testing, DevOps metrics and enterprise DevOps. - [Elastic Leadership](https://www.elasticleadership.com): Elastic Leadership, Roy's framework for software team leaders (survival, learning and self-organizing modes). ### Social - X: https://x.com/royosherove - LinkedIn: https://www.linkedin.com/in/osherove/ ### Contact - Contact form: https://forms.gle/jQSx5KoXvrVA1JCK7 ### License Licensed under CC BY 4.0 — reuse, quote and adapt with attribution (Roy Osherove, link to the original page). License: https://creativecommons.org/licenses/by/4.0/