SQL Date Functions Reference
Traditional SQL (T-SQL / SQL Server) vs. Spark SQL — Syntax, Examples & Outputs
1■. T-SQL (SQL Server) Date Functions
Function Syntax Example Output
YEAR / MONTH / DAY YEAR(date) SELECT YEAR('2026-07-21'), 2026 | 7 | 21
MONTH(date) MONTH('2026-07-21'),
DAY(date) DAY('2026-07-21')
DATEPART DATEPART(datepart, date) SELECT DATEPART(YEAR,'2026-07-21'), 2026 | 3 | 30
DATEPART(QUARTER,'2026-07-21'),
DATEPART(WEEK,'2026-07-21')
DATENAME DATENAME(datepart, date) SELECT DATENAME(MONTH,'2026-07-21'), 'July' | 'Tuesday'
DATENAME(WEEKDAY,'2026-07-21')
DATETRUNC DATETRUNC(datepart, date) SELECT DATETRUNC(MONTH,'2026-07-21'), 2026-07-01 | 2026-01-01
(SQL Server 2022+) DATETRUNC(YEAR,'2026-07-21')
EOMONTH EOMONTH(date [, SELECT EOMONTH('2026-07-21'), 2026-07-31 | 2026-08-31
months_to_add]) EOMONTH('2026-07-21', 1)
FORMAT FORMAT(value, SELECT '2026/07/21' | '1,234.50'
format_string) FORMAT('2026-07-21','yyyy/MM/dd'),
FORMAT(1234.5,'N2')
CONVERT CONVERT(data_type, expr, SELECT CONVERT(VARCHAR, '2026-07-21', '21/07/2026'
style) 103)
TRY_CONVERT TRY_CONVERT(data_type, SELECT TRY_CONVERT(INT,'abc'), NULL | 123
expr) TRY_CONVERT(INT,'123')
CAST CAST(expr AS data_type) SELECT CAST('2026-07-21' AS DATE), 2026-07-21 | '123'
CAST(123 AS VARCHAR)
DATEADD DATEADD(datepart, number, SELECT DATEADD(DAY, 10, '2026-07-21'), 2026-07-31 | 2026-06-21
date) DATEADD(MONTH, -1, '2026-07-21')
DATEDIFF DATEDIFF(datepart, start, SELECT 20 | 6
end) DATEDIFF(DAY,'2026-07-01','2026-07-21'),
DATEDIFF(MONTH,'2026-01-01','2026-07-21'
)
Function Syntax Example Output
ISDATE ISDATE(expression) SELECT ISDATE('2026-07-21'), 1|0
ISDATE('not a date')
2. Spark SQL Equivalent Functions
Function Syntax Example Output
year / month / year(date) SELECT year('2026-07-21'), 2026 | 7 | 21
dayofmonth month(date) month('2026-07-21'),
dayofmonth(date) dayofmonth('2026-07-21')
date_part / extract date_part(field, date) SELECT date_part('YEAR','2026-07-21'), 2026 | 3 | 30
date_part('QUARTER','2026-07-21'),
date_part('WEEK','2026-07-21')
date_format (name date_format(date, 'MMMM') SELECT date_format('2026-07-21','MMMM'), 'July' | 'Tuesday'
form) date_format(date, 'EEEE') date_format('2026-07-21','EEEE')
date_trunc date_trunc(format, SELECT date_trunc('MONTH','2026-07-21'), 2026-07-01 00:00:00 |
timestamp) date_trunc('YEAR','2026-07-21') 2026-01-01 00:00:00
last_day (+ last_day(date) SELECT last_day('2026-07-21'), 2026-07-31 | 2026-08-31
add_months) add_months(date, n) last_day(add_months('2026-07-21',1))
date_format / date_format(date, format) SELECT '2026/07/21' | '1,234.50'
format_number format_number(value, d) date_format('2026-07-21','yyyy/MM/dd'),
format_number(1234.5, 2)
date_format (style-free) date_format(date, SELECT '21/07/2026'
pattern) date_format(current_date(),'dd/MM/yyyy')
try_cast try_cast(expr AS SELECT try_cast('abc' AS INT), NULL | 123
data_type) try_cast('123' AS INT)
CAST CAST(expr AS data_type) SELECT CAST('2026-07-21' AS DATE), 2026-07-21 | '123'
CAST(123 AS STRING)
date_add / add_months date_add(date, days) SELECT date_add('2026-07-21', 10), 2026-07-31 | 2026-06-21
add_months(date, months) add_months('2026-07-21', -1)
datediff / datediff(end, start) SELECT 20 | 6.6774
months_between months_between(end, datediff('2026-07-21','2026-07-01'),
start) months_between('2026-07-21','2026-01-01'
)
try_cast (as ISDATE try_cast(expr AS DATE) IS SELECT CASE WHEN try_cast('2026-07-21' 1|0
check) NOT NULL AS DATE)
IS NOT NULL THEN 1 ELSE 0 END,
CASE WHEN try_cast('not a date' AS DATE)
IS NOT NULL THEN 1 ELSE 0 END
Notes: Spark SQL has no direct CONVERT/DATENAME/DATEPART/ISDATE/EOMONTH-with-offset functions — the table above shows the closest functional equivalents (date_format,
extract/date_part, try_cast, last_day + add_months). Output columns show illustrative results for the given example (dates based on 2026-07-21, a Tuesday).