Translation to sql server

[eric-fey-hus]

Hi,

I want to use the transpile function to translate postgres to sql server. I am using write="tsql", but still get errors. For example, it translates "date_part('year', start_date)" to "EXTRACT(year FROM start_date)". But what I need is "YEAR(start_date)".

How can I define my own "translation" of this in a custom dialect?

Kind regards, Eric

[georgesittas]

Hey!

Is EXTRACT invalid T-SQL, or is it just a matter of preference? If it's invalid, then this is something we should probably fix in SQLGlot, so please create an issue in that case and we'll solve it ASAP.

How can I define my own "translation" of this in a custom dialect?

You can override almost everything in SQLGlot, including the TSQL class itself. You'd need to map the syntax node produced by parsing DATE_PART in postgres to the corresponding YEAR(...) call.

[georgesittas]

Ok I just verified in SQL Fiddle that EXTRACT is invalid. I'll work towards a fix for this.

[eric-fey-hus]

Thanks for the quick answer. I created an issue. Concerning the override you mentioned. How does that work? I have looked at the code of the custom dialect and tried to figure it out, but I could not find documentation that makes this clear to me. Is there an example somewhere?

[georgesittas]

Yes, look e.g. at how Redshift is implemented: it subclasses Postgres (since they have similarities) and then it overrides those methods that need to be further specialized to Redshift. Similarly, you could subclass from Dialect itself which represents a "unified", SQLGlot dialect, and extend it to cover your use case.

We're planning to improve the documentation in the next months, so hopefully there will be more examples to look at soon.

[georgesittas]

Fixed in 83db4c0