builtin functions in T-SQL SQL
time + date +
Spark
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')
3 DATEPART DATEPART(datepart, date) SELECT DATEPART(YEAR,'2026-07-21'), 2026 | 3 | 30
march-
>
= DATEPART(QUARTER,'2026-07-21'),
Mednesday > 3
, DATEPART(WEEK,'2026-07-21')
day/weard
-
+
DATENAME
'
month/
DATENAME(datepart, date) SELECT DATENAME(MONTH,'2026-07-21'), 'July' | 'Tuesday'
b- Friday %
DATENAME(WEEKDAY,'2026-07-21')
DATETRUNC DATETRUNC(datepart, date) SELECT DATETRUNC(MONTH,'2026-07-21'), 2026-07-01 | 2026-01-01
Rolldown oop2 Toa J l go? .
(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'
/ _
58941, sis
.
format_string) FORMAT('2026-07-21','yyyy/MM/dd'),
FORMAT(1234.5,'N2')
dd-mm-gzgy
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
1 01915 sig
#
expr) TRY_CONVERT(INT,'123')
,
das85 CAST CAST(expr AS data_type) SELECT CAST('2026-07-21' AS DATE),
CAST(123 AS VARCHAR)
2026-07-21 | '123'
Nic's
DATEADD DATEADD(datepart, number, SELECT DATEADD(DAY, 10, '2026-07-21'), 2026-07-31 | 2026-06-21
L'isilos
date) DATEADD(MONTH, -1, '2026-07-21')
--
DATEDIFF DATEDIFF(datepart, start, SELECT 20 | 6
end) DATEDIFF(DAY,'2026-07-01','2026-07-21'),
gi
DATEDIFF(MONTH,'2026-01-01','2026-07-21'
)
- b/s, Nwi) .
Function Syntax Example Output
√ ISDATE ISDATE(expression) SELECT ISDATE('2026-07-21'), 1|0
ISDATE('not a date')
to run
built-in store procedure5
EXEC sp_columns 'users' > -
------------------------------------------ WAY to change the type of the column with the below method
SELECT *
FROM users
SELECT
ALTER TABLE users add new_date DATE review_date,
UPDATE users
DATETRUNC(month, review_date),
set new_date = TRY_CAST(C6 as DATE); EOMONTH(review_date)
FROM reviews
ALTER TABLE users drop column C6
EXEC sp_rename 'users.new_date', 'C6', 'COLUMN'
EXEC sp_columns 'reviews' EXEC sp_rename
WITH conversion_date AS
ALTER TABLE reviews ADD new_date datetime (
SELECT
UPDATE reviews review_date,
SET new_date = TRY_CAST(review_date as DATETIME2) TRY_CAST(review_date as DATE) as 'as_date',
TRY_CAST(review_date as TIME) as 'as_time',
TRY_CONVERT(DATE, review_date, 103) as 'conversion_d',
ALTER TABLE reviews DROP COLUMN review_date TRY_CONVERT(TIME, review_date, 103) as 'conversion_t'
FROM reviews
EXEC sp_rename 'reviews.new_date', 'review_date', 'COLUMN' )
!
SELECT
*,
FORMAT(as_date, 'dd/MM/yyyy') as 'formatt'
SELECT FROM conversion_date
C6,
DAY(C6) as 'day',
MONTH(C6) as 'month',
YEAR(C6) as 'year',
DATEADD(day, 2, C6) as three_days, Store &
DATEADD(month, 3, C6) as three_months,
EXECsy
DATENAME(weekday , C6),
DATENAME(month, C6),
sis's proceduves
DATENAME(quarter, C6),
ja Miss
DATEADD(weekday , 2, C6)
from users ,
·
jijdsP ‰ ※
Ja 94 Os
∆
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
form)
(name date_format(date, 'MMMM')
date_format(date, 'EEEE')
SELECT date_format('2026-07-21','MMMM'),
date_format('2026-07-21','EEEE')
'July' | 'Tuesday'
√ date_trunc date_trunc(format,
timestamp)
SELECT date_trunc('MONTH','2026-07-21'),
date_trunc('YEAR','2026-07-21')
2026-07-01 00:00:00 |
2026-01-01 00:00:00
darvetrunt
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).