diff options
Diffstat (limited to 'sqlglot/dialects/tsql.py')
| -rw-r--r-- | sqlglot/dialects/tsql.py | 78 |
1 files changed, 63 insertions, 15 deletions
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py index 107ace7..0f93c75 100644 --- a/sqlglot/dialects/tsql.py +++ b/sqlglot/dialects/tsql.py @@ -1,5 +1,7 @@ +import re + from sqlglot import exp -from sqlglot.dialects.dialect import Dialect, rename_func +from sqlglot.dialects.dialect import Dialect, parse_date_delta, rename_func from sqlglot.expressions import DataType from sqlglot.generator import Generator from sqlglot.helper import list_get @@ -27,6 +29,11 @@ DATE_DELTA_INTERVAL = { } +DATE_FMT_RE = re.compile("([dD]{1,2})|([mM]{1,2})|([yY]{1,4})|([hH]{1,2})|([sS]{1,2})") +# N = Numeric, C=Currency +TRANSPILE_SAFE_NUMBER_FMT = {"N", "C"} + + def tsql_format_time_lambda(exp_class, full_format_mapping=None, default=None): def _format_time(args): return exp_class( @@ -42,26 +49,40 @@ def tsql_format_time_lambda(exp_class, full_format_mapping=None, default=None): return _format_time -def parse_date_delta(exp_class): - def inner_func(args): - unit = DATE_DELTA_INTERVAL.get(list_get(args, 0).name.lower(), "day") - return exp_class(this=list_get(args, 2), expression=list_get(args, 1), unit=unit) - - return inner_func +def parse_format(args): + fmt = list_get(args, 1) + number_fmt = fmt.name in TRANSPILE_SAFE_NUMBER_FMT or not DATE_FMT_RE.search(fmt.this) + if number_fmt: + return exp.NumberToStr(this=list_get(args, 0), format=fmt) + return exp.TimeToStr( + this=list_get(args, 0), + format=exp.Literal.string( + format_time(fmt.name, TSQL.format_time_mapping) + if len(fmt.name) == 1 + else format_time(fmt.name, TSQL.time_mapping) + ), + ) -def generate_date_delta(self, e): +def generate_date_delta_with_unit_sql(self, e): func = "DATEADD" if isinstance(e, exp.DateAdd) else "DATEDIFF" return f"{func}({self.format_args(e.text('unit'), e.expression, e.this)})" +def generate_format_sql(self, e): + fmt = ( + e.args["format"] + if isinstance(e, exp.NumberToStr) + else exp.Literal.string(format_time(e.text("format"), TSQL.inverse_time_mapping)) + ) + return f"FORMAT({self.format_args(e.this, fmt)})" + + class TSQL(Dialect): null_ordering = "nulls_are_small" time_format = "'yyyy-mm-dd hh:mm:ss'" time_mapping = { - "yyyy": "%Y", - "yy": "%y", "year": "%Y", "qq": "%q", "q": "%q", @@ -101,6 +122,8 @@ class TSQL(Dialect): "H": "%-H", "h": "%-I", "S": "%f", + "yyyy": "%Y", + "yy": "%y", } convert_format_mapping = { @@ -143,6 +166,27 @@ class TSQL(Dialect): "120": "%Y-%m-%d %H:%M:%S", "121": "%Y-%m-%d %H:%M:%S.%f", } + # not sure if complete + format_time_mapping = { + "y": "%B %Y", + "d": "%m/%d/%Y", + "H": "%-H", + "h": "%-I", + "s": "%Y-%m-%d %H:%M:%S", + "D": "%A,%B,%Y", + "f": "%A,%B,%Y %-I:%M %p", + "F": "%A,%B,%Y %-I:%M:%S %p", + "g": "%m/%d/%Y %-I:%M %p", + "G": "%m/%d/%Y %-I:%M:%S %p", + "M": "%B %-d", + "m": "%B %-d", + "O": "%Y-%m-%dT%H:%M:%S", + "u": "%Y-%M-%D %H:%M:%S%z", + "U": "%A, %B %D, %Y %H:%M:%S%z", + "T": "%-I:%M:%S %p", + "t": "%-I:%M", + "Y": "%a %Y", + } class Tokenizer(Tokenizer): IDENTIFIERS = ['"', ("[", "]")] @@ -166,6 +210,7 @@ class TSQL(Dialect): "SQL_VARIANT": TokenType.VARIANT, "NVARCHAR(MAX)": TokenType.TEXT, "VARCHAR(MAX)": TokenType.TEXT, + "TOP": TokenType.TOP, } class Parser(Parser): @@ -173,8 +218,8 @@ class TSQL(Dialect): **Parser.FUNCTIONS, "CHARINDEX": exp.StrPosition.from_arg_list, "ISNULL": exp.Coalesce.from_arg_list, - "DATEADD": parse_date_delta(exp.DateAdd), - "DATEDIFF": parse_date_delta(exp.DateDiff), + "DATEADD": parse_date_delta(exp.DateAdd, unit_mapping=DATE_DELTA_INTERVAL), + "DATEDIFF": parse_date_delta(exp.DateDiff, unit_mapping=DATE_DELTA_INTERVAL), "DATENAME": tsql_format_time_lambda(exp.TimeToStr, full_format_mapping=True), "DATEPART": tsql_format_time_lambda(exp.TimeToStr), "GETDATE": exp.CurrentDate.from_arg_list, @@ -182,6 +227,7 @@ class TSQL(Dialect): "LEN": exp.Length.from_arg_list, "REPLICATE": exp.Repeat.from_arg_list, "JSON_VALUE": exp.JSONExtractScalar.from_arg_list, + "FORMAT": parse_format, } VAR_LENGTH_DATATYPES = { @@ -194,7 +240,7 @@ class TSQL(Dialect): def _parse_convert(self, strict): to = self._parse_types() self._match(TokenType.COMMA) - this = self._parse_field() + this = self._parse_column() # Retrieve length of datatype and override to default if not specified if list_get(to.expressions, 0) is None and to.this in self.VAR_LENGTH_DATATYPES: @@ -238,8 +284,10 @@ class TSQL(Dialect): TRANSFORMS = { **Generator.TRANSFORMS, - exp.DateAdd: lambda self, e: generate_date_delta(self, e), - exp.DateDiff: lambda self, e: generate_date_delta(self, e), + exp.DateAdd: generate_date_delta_with_unit_sql, + exp.DateDiff: generate_date_delta_with_unit_sql, exp.CurrentDate: rename_func("GETDATE"), exp.If: rename_func("IIF"), + exp.NumberToStr: generate_format_sql, + exp.TimeToStr: generate_format_sql, } |