Details
-
Improvement
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.8.0
-
None
Description
The TO_DATE() is exposing the Joda string formatting conventions into the SQL layer. This is not following SQL conventions used by ANSI and many other database engines on the market.
Add new UDFs:
- sql_to_date(String, Format),
- sql_to_time(String, Format),
- sql_to_timestamp(String, Format)
that requires Postgres datetime format.
Table of supported Postgres patterns
| Pattern name | Postgres format |
|---|---|
| Full name of day | day |
| Day of year | ddd |
| Day of month | dd |
| Day of week | d |
| Name of month | month |
| Abr name of month | mon |
| Full era name | ee |
| Name of day | dy |
| Time zone | tz |
| Hour 12 | hh |
| Hour 12 | hh12 |
| Hour 24 | hh24 |
| Minute of hour | mi |
| Second of minute | ss |
| Millisecond of minute | ms |
| Week of year | ww |
| Month | mm |
| Halfday am | am |
| Year | y |
| ref. | https://www.postgresql.org/docs/8.2/static/functions-formatting.html |
Table of acceptable Postgres pattern modifiers, which may be used in Format string
| Description | Pattern |
|---|---|
| fill mode (suppress padding blanks and zeroes) | fm |
| fixed format global option (see usage notes) | fx |
| translation mode (print localized day and month names based on lc_messages) | tm |
| spell mode (not yet implemented) | sp |
| ref. | https://www.postgresql.org/docs/8.2/static/functions-formatting.html |
Attachments
Issue Links
- is related to
-
DRILL-4939 to_date function returns incorrect result
-
- Resolved
-
- links to
