How to load SQL pure Date type.

[janphilippelavoiefq]

Question

We are using Prisma to read from an already existing SQL database.
This Database has a lot of Date fields (not DateTime)

Prisma seems to be loading those values by calling new Date()

The issue with this is that javascript assumes the pure Date is midnight UTC when calling new, but uses server timezone for other operations.

So Date.new("2000-01-01").getFullYear() returns 1999 if your sever is anywhere west of London.

Which means that all our dates are wrong after being loaded by Prisma.

Am using it wrong somehow? Anyway to add custom loaders?

How to reproduce (optional)

Expected behavior (optional)

No response

Information about Prisma Schema, Client Queries and Environment (optional)

// Add your schema.prisma

// Add any relevant Prisma Client queries here

• OS:

• Database:

• Node.js version:

• Run prisma -v to see your Prisma version and paste it

[nurul3101]

Hey!

In the schema are you currently mapping SQL DATE to DateTime @db.Date? (Reference)

You can set TZ=UTC as an environment variable before your Node process starts. This makes local and UTC methods return the same thing. Simple but can have side effects if you actually need local time elsewhere.

[janphilippelavoiefq]

Hey!

I thought about that but it will have some side effects on the rest of our application.
That might still be the best route though.
So just to confirm, there is no way to have custom loaders, or anything that can automatically run after a query to change the resulting model?

[nurul3101]

No, I think setting TZ to UTC is the best approach.

[DucMinhNe]

You're not using Prisma wrong, and the data in the DB is fine — this is a pure JavaScript timezone gotcha, not a Prisma bug.

What's actually happening

A SQL DATE column maps to Prisma's DateTime scalar with the @db.Date native type, and Prisma Client hands you a normal JS Date set to midnight UTC. So a row holding 2000-01-01 comes back as:

2000-01-01T00:00:00.000Z

That value is correct. The off-by-one only appears when you read it with the local getters, because those re-interpret that instant in the process timezone:

const d = new Date("2000-01-01T00:00:00.000Z"); // what Prisma returns
// process running in America/Los_Angeles:
d.getFullYear();    // 1999  ❌ (local: still Dec 31 1999 there)
d.getUTCFullYear(); // 2000  ✅
d.toISOString().slice(0, 10); // "2000-01-01" ✅

So nothing is lost — you just have to read the date in UTC, since UTC is the timezone Prisma encoded the date-only value in.

Fix 1 — read with UTC getters (or format in UTC)

Anywhere you pull components off the date, use the getUTC* family, or format with timeZone: 'UTC':

d.getUTCFullYear();   // 2000
d.getUTCMonth();      // 0
d.getUTCDate();       // 1

new Intl.DateTimeFormat('en-CA', { timeZone: 'UTC' }).format(d); // "2000-01-01"

Fix 2 — add a "custom loader" via a Prisma Client result extension

There's no per-field decoder hook in the engine, but result extensions are exactly the supported way to transform a value on read. Add a computed field that always returns the correct date-only value, derived from the UTC parts so it's timezone-proof:

const prisma = new PrismaClient().$extends({
  result: {
    event: { // your model name
      eventDate: { // your @db.Date field — pick any name for the computed one
        needs: { date: true },
        compute(row) {
          // row.date is the UTC-midnight Date; format it in UTC
          return row.date.toISOString().slice(0, 10); // "2000-01-01"
          // or return a "floating" local-midnight Date if you prefer a Date:
          // const d = row.date;
          // return new Date(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate());
        },
      },
    },
  },
});

Computed fields are type-safe and computed on access (docs). The constraint is they must be based on scalar fields (which date is), and they're added as a new field — keep the original date and read eventDate, or name the computed field something like dateOnly.

Fix 3 — global TZ=UTC (the blunt instrument)

Setting TZ=UTC before the Node process starts (as nurul3101 mentioned) makes the local getters agree with UTC, so .getFullYear() returns 2000 again. It's the simplest fix, but it's process-wide, so only do it if your whole app is happy operating in UTC. The result-extension approach is the surgical version that leaves the rest of your date handling untouched.

TL;DR: the stored date is right; treat @db.Date values as UTC and read them with getUTC* / timeZone: 'UTC', and use a result extension if you want a clean date-only field at the read boundary.