I'll use my own, thank you very much

I couldn't think of anything to add to this story from Mohammad Abdulfatah, so here goes ...

I was tasked with writing a report against the backend database of a widely-used and highly-respected library automation system. I needed to display a field aptly named “creation_date”. I initially assumed that the field in question would be of type “datetime”, but I was wrong. It was of an integer type, and a nullable one at that.

I tried to reverse engineer the data format, but that got me no where. I even considered that it might be stored in Binary-Packed Decimal (BPD) format (a relic of the 8086 assembly language), but still no luck.

As a last attempt, I looked at the insert trigger, and was enlightened by the following SQL statement:

update item
set creation_date = datediff (dd, '1 Jan 1970', getdate() )
from item, inserted
where item.item# = inserted.item#

Now it all made sense. The database designer chose to store the date as the number of days since January 1st, 1970. I guess one can’t trust those database servers to store the date in a more efficient form.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!