SQL Server CONVERT date styles in Snowflake, Databricks and BigQuery
Every common SQL Server CONVERT date style code with the equivalent format string for Snowflake TO_CHAR, Databricks date_format and BigQuery FORMAT_DATETIME. Use them with CONVERT and FORMAT rewrites.
Snowflake: TO_CHAR(d, 'format') · Databricks: date_format(d, 'pattern') · BigQuery: FORMAT_DATETIME('format', d)
| Style | SQL Server output | Snowflake | Databricks | BigQuery |
|---|---|---|---|---|
| 101 | MM/dd/yyyy | MM/DD/YYYY | MM/dd/yyyy | %m/%d/%Y |
| 102 | yyyy.MM.dd | YYYY.MM.DD | yyyy.MM.dd | %Y.%m.%d |
| 103 | dd/MM/yyyy | DD/MM/YYYY | dd/MM/yyyy | %d/%m/%Y |
| 104 | dd.MM.yyyy | DD.MM.YYYY | dd.MM.yyyy | %d.%m.%Y |
| 105 | dd-MM-yyyy | DD-MM-YYYY | dd-MM-yyyy | %d-%m-%Y |
| 106 | dd MMM yyyy | DD MON YYYY | dd MMM yyyy | %d %b %Y |
| 107 | MMM dd, yyyy | MON DD, YYYY | MMM dd, yyyy | %b %d, %Y |
| 108 | HH:mm:ss | HH24:MI:SS | HH:mm:ss | %H:%M:%S |
| 110 | MM-dd-yyyy | MM-DD-YYYY | MM-dd-yyyy | %m-%d-%Y |
| 111 | yyyy/MM/dd | YYYY/MM/DD | yyyy/MM/dd | %Y/%m/%d |
| 112 | yyyyMMdd | YYYYMMDD | yyyyMMdd | %Y%m%d |
| 114 | HH:mm:ss:fff | HH24:MI:SS:FF3 | HH:mm:ss:SSS | %H:%M:%E3S |
| 120 | yyyy-MM-dd HH:mm:ss | YYYY-MM-DD HH24:MI:SS | yyyy-MM-dd HH:mm:ss | %Y-%m-%d %H:%M:%S |
| 121 | yyyy-MM-dd HH:mm:ss.fff | YYYY-MM-DD HH24:MI:SS.FF3 | yyyy-MM-dd HH:mm:ss.SSS | %Y-%m-%d %H:%M:%E3S |
| 23 | yyyy-MM-dd | YYYY-MM-DD | yyyy-MM-dd | %Y-%m-%d |
| 126 | yyyy-MM-ddTHH:mm:ss.fff | YYYY-MM-DD"T"HH24:MI:SS.FF3 | yyyy-MM-dd'T'HH:mm:ss.SSS | %Y-%m-%dT%H:%M:%E3S |
Things to check
- Month names (styles 106 and 107) follow the SQL Server session language. The targets always print English abbreviations such as Jan and Feb.
- The two-digit-year styles 1 to 14 drop the century. Prefer the four-digit form in the target unless a downstream system needs two digits; the patterns are the same with YY, yy or %y.
- Databricks patterns are case-sensitive:
MMis month andmmis minute,HHis 24-hour andhhis 12-hour. - Literal text goes in double quotes in Snowflake (
"T") and in single quotes in Databricks ('T'). BigQuery copies any character that is not a % element. - For DATE values in BigQuery use
FORMAT_DATE; it accepts the same date elements asFORMAT_DATETIME.