search

sqlkata / querybuilder

SQL query builder, written in c#, helps you build complex queries easily, supports SqlServer, MySql, PostgreSql, Oracle, Sqlite and Firebird
https://sqlkata.com
MIT License
3.08k stars 498 forks source link

Problem with Date format in SqlServer #208

Open JordiPerello opened 5 years ago

JordiPerello commented 5 years ago

When we have fields of type Date, the SqlKata.Compile() converts it to the format "YYYY-DD-MM" instead of "YYYY-MM-DD", depending on the configuration of your DB, this causes an error when executing the SqlServer statement

I think it is important to work with the culture or in any case work with ISO-8601, which does not care about the configuration that we have in our database, since it is a standard format that any SqlServer recognizes without problems, instead of "YYYY-MM-DD", we removed the scripts and it would be"YYYYMMDD", this is enough to stop giving problems in any query.

There is an email that I have sent to Ahmad Moussawi, in which I explain the problem with examples and images.

Please, I hope you can find the problem, because at the moment this causes us errors in our application and we have to do special processes to solve it and we have a bad code

Thanks, I think you're doing a great job with this framework

ahmad-moussawi commented 5 years ago

Maybe providing input formatters can solve this, something like:

db.Format(typeof(DateTime), (value) => value.ToString("yyyyMMdd"));
db.Format(typeof(int), (value) => value.ToString("N3"));
rolivares commented 2 years ago

same case here!, would be a great enhancement could provide a serie of format providers or even setting some type of configuration in order to inform to the Compiler class wich is the target configuration, for example in the explicit SqlServerCompiler instanciation.