Every shift starts at midnight: how a time column quietly loses its time

A hospital keeps its wards in a master data entity. Each ward carries an opening time and a cut-off for same-day referrals. Somebody exports the entity to CSV, corrects four ward names in Excel, and imports the file back.

The names are right. Every opening time now reads 00:00.

No row was refused. No rule failed. Nothing landed in the error log, the import summary said 1,482 rows updated, and it was telling the truth. Midnight is a perfectly good time.

That is the trouble with this fault. A lost time does not look like a lost value. It looks like a value.

Why nothing you already run catches it

A truncated string looks truncated. A number in a text field gives itself away the moment you sort the column. Every data quality check you own is built to catch that kind of damage: nulls, types, lengths, ranges, row counts.

00:00:00 passes all of them. It is a real datetime. It sorts, it exports, it survives a type check, and it reads as a time to any system downstream. Your row count matches because no rows were lost. Your null check passes because nothing is null.

You find out weeks later, when a nurse asks why the referral cut-off for her ward is the middle of the night.

A time column is three separate decisions

Each one can drop the hour, and they fail in different ways.

The first decision is a design choice. In Primentra the format library holds it: a mask carrying HH:mm means the column stores and shows a time, and a mask without one means any time is dropped on save. One rule, set in one place, shared by every column pointing at that format.

The third decision is not a design choice at all. It is a promise every write path has to keep, and there are six of them: the grid, a paste, a file import, a staging batch, an approved change and the REST API. We broke that promise in two, and neither break made a sound.

The round trip that ate itself

Our own CSV export writes 2026-01-31 14:30:00. Importing that exact file back stored midnight.

The import parsed every text cell against one hard-coded date-only mask. The parser does catch a time on its ISO path, and then the assembler throws it away, because the mask it was handed names no hour. So export, fix in Excel, import, the most ordinary operation in master data, reset a whole column to 00:00 without a word.

A second version of it sat one separator away and was harder to spot. A mask naming seconds, YYYY-MM-DD HH:mm:ss, wrapped everything from the first time token in a single optional group. All or nothing. A value written only to the minute matched the date part and lost the entire time, so two columns whose masks differed by :ss behaved differently on the same file.

Both are fixed in 1.2026.9.17. The column format now decides whether the time is kept, which is what the grid had been doing all along.

One thing the import still refuses to take from the column, on purpose: the day order. A date in a file is read as ISO, never in the column's own mask, because the file cannot see that setting and 01-02-2026 is the first of February or the second of January depending on where it lands. Typing and pasting do follow the column format, because there a person sees the result before it is saved.

The one that depended on where the column sat

This is the better story, and the reason I wanted to write any of it down.

A staging batch stored a time column as 00:00, unless that column happened to be the last attribute in the entity.

The batch procedure walks the attribute list three times: once to validate, once to insert, once to update. Only the validation cursor fetched the flag that says whether a column's format carries a time. The insert and update cursors read that variable without ever fetching it, and SQL Server leaves a variable exactly as it was when a FETCH supplies nothing. Both writing loops therefore ran on whatever flag the validation loop had left behind, and applied that single answer to every DateTime column in the batch.

Validation accepted 07:30 without a murmur. The write then cast it through DATE. Nothing errored, because nothing was wrong with the value. The write threw it away.

Carry this part to your own system. Moving the column to the end of the entity looked like a cure. It was not. With a time-bearing column last, every date-only column in the batch now kept a time it was meant to drop. The fault had not gone anywhere, it had swapped which half of the columns it damaged. A workaround that moves the symptom is a workaround that hides it.

The flag is now worked out once into a temp table and joined into all three cursors, so the rule has one definition instead of three. And because this shape of fault cannot be seen by reading the output, a build check fails the release on it: a variable that a cursor reads but never fetches, reported only when another cursor in the same procedure does fetch it. We proved it red against the real bug before letting it go green.

Find it in your own system tonight

This is not a Primentra shape. Any tool that stores a time in a datetime column can flatten one, and the check is the same everywhere: count the values sitting at exactly midnight in a column that is supposed to carry an hour.

Against a Primentra database, that reads:

-- Time-bearing columns, and how many of their
-- values sit at exactly midnight.
SELECT    e.Name                 AS EntityName,
          a.DisplayName          AS ColumnName,
          COUNT(v.DateTimeValue) AS ValuesStored,
          SUM(CASE WHEN CAST(v.DateTimeValue AS TIME)
                        = '00:00:00'
                   THEN 1 ELSE 0 END) AS AtMidnight
FROM      Attributes a
JOIN      Entities e         ON e.Id = a.EntityId
LEFT JOIN DateTimeFormats f  ON f.Id = a.DateTimeFormatId
JOIN      EntityValues v     ON v.AttributeId = a.Id
WHERE     a.DataType = 'DateTime'
  AND     ISNULL(f.HasTime, 0) = 1
GROUP BY  e.Name, a.DisplayName
ORDER BY  AtMidnight DESC;

Read the result by its spread rather than its total. A column where every stored value sits at midnight was never carrying a time, whatever its format claims. A column where most values carry an hour and one block sits at 00:00 is the signature of a bad load. Pull the last-modified stamps on those rows and you have the date of the batch that did it.

Some of that count is honest. Three kinds, in fact. A night shift that starts at 00:00. A column somebody pointed at a date-only mask on purpose. And every record that existed before a time was added to the format, which reads 00:00 because no time was ever captured for it. Rule those out before you go looking for a culprit.

What to ask in a demo

Do not ask whether a tool supports times. Everything supports times. Ask these instead, and watch what the demo does.

Which routes write this column, and do they all read the same setting? Name the doors out loud, grid and paste and file and batch and API and approval, then ask where the rule about carrying a time lives. One definition shared by six routes is a different product from six routes with their own opinion, and the second looks identical until the day it does not.

What survives a round trip? Export a row with a time, import the same file straight back without touching it, read the value. Four minutes. It is the test we failed, and it is the one nobody runs because it feels too obvious to bother with.

Last one. Put a column masked to seconds in front of a value written only to the minute. It should keep the minutes. If it answers midnight, every hand-typed value in every file you ever load is at risk.

Midnight is a good hiding place

It is a valid value, so every guard you own waves it through, and every system downstream reads it as a time somebody meant. No monitor will page you about it.

The only thing that notices is a person who knows the ward opens at half past seven. Run the query before they do.

Two neighbours, if you want them: the same one-rule-every-route argument drove the audit in the post about every route into the product, and the rules that check a date rather than store one are in date validation rules.

Common questions