Hey everyone, I am using Prisma with Next.js and an SQLite database. I’m having a lot of trouble with the DateTime data. If I define the field in the schema using the

DateTime

type, I see this when making my query:

Inconsistent column data: Conversion failed: input contains invalid characters

I also try defining it as a string to see if I could just get the raw string output of

7/28/22 4:56:51 PM

, that didn’t work. I tried adding it as an unsupported type and using

queryRaw

, but I get:

Invalid `prisma.$queryRaw()` invocation:


Raw query failed. Code: `N/A`. Message: `N/A`

Any ideas? edit: I ran the same sql query directly on the db to make sure the sql was valid and it worked just fine.

Hi. you are not using iso8601 for date formats in the DB?

Unfortunately the DB is generated from an application that I have no control of. I’m not sure what format it is.

the best practice when it comes to storing dates in the DB is to use iso8601 strings. I guess you can try to look at localization issues here to see if it's a case of mismatching localizations. you can also try to use a convert function with moment.js (or other libs) and prisma $use (but I'm not so sure about this strategy)

Yeah, the person who built the application that outputs the db is not really willing to help. I can’t really see what he’s doing on his end. Okay, I’ll take a look at the $use docs and see if I can figure something out. Thanks

GL!

I’m not sure it’ll work, as prisma fails as soon as it tries to make the query..

I had the same thought. that's why I said I'm not too sure about this strategy.

Not sure what you mean, sorry. Could you elaborate?

alright. not sure how i’d use this on the prisma side but I can try and explore

most DB's got native date functions that allows you to convert dates to strings and vice versa. you can try to maybe convert it to iso8601 somehow. select from select after applying the conversion to iso8610 or a view table

oh interesting. let me give that a shot thanks!

if I remember my sql days correctly you can transform a table into a view table after applying the date conversion (it can have the same schema properties)

this would be outside of prisma, right? so I’d need to run some action before spinning up my application

yes... prisma will query the view table

sorry - i’m typically very front end heavy so thsi stuff is unfamiliar to me. thanks

me too now days - but I did a lot of orm work in the past with NH (I was an NH pro back when it was still relevant)