Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Convert Oracle Date Functions to SQL Server Date Functions
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

Function

Oracle

SQL Server

Date addition

p1 + p2

DATEADD(DD, p2, p1)

Date subtraction

p1 – p2

DATEDIFF(DD, p2, p1)

Add Months

ADD_MONTHS (p1, p2)

DATEADD(MM, p2, p1)

Last day of the month

LAST_DAY(p1)

SELECT DATEADD(DD, -1, DATEADD(MM, 1, DATEADD(MM, DATEDIFF(MM, 0, p1), 0)))

Time zone conversion

NEW_TIME(p1, p2, p3)

Not Available

First Weekday After Date

NEXT_DAY(p1, p2)

Not Available

Convert Date to String

TO_CHAR(p1, p2)

DATENAME

Convert String to Date

TO_DATE(p1)

CAST(p1 AS DATETIME)

CONVERT(DATETIME, p1)

Get Current Date and Time

SYSDATE

CURRENT_DATE

SYSTIMESTAMP

LOCALTIMESTAMP

CURRENT_TIMESTAMP

GETDATE()

SYSDATETIME()

SYSDATETIMEOFFSET()

Convert Date/Time to Date/Time with Time Zone

FROM_TZ(p1, p2)

TODATETIMEOFFSET(p1, p2)

Get Number of Months Between 2 Dates

MONTHS_BETWEEN(p1, p2)

DATEDIFF( MONTH, CAST(p2 AS float), CAST( DATEADD(DAY, ( -CAST(DATEPART(DAY, p2) AS float(53)) + 1 ), p1) AS float))

Round a Date to a Specific Unit of Measure

ROUND(p1)

ROUND(p1, p2)

Not Available

Truncate a Date to a Specific Unit of Measure

TRUNC(p1)

SELECT DATEADD(DD, DATEDIFF(DD, 0, p1), 0)

Truncate Date to a Specific Unit of Measure

TRUNC(p1, p2)

SELECT DATEADD(p2, DATEDIFF(p2, 0, p1), 0)

Time Zone Offset

TZ_OFFSET(p1)

Not Available

Session Time Zone

SESSIONTIMEZONE

Not Available

Database Time Zone

DBTIMEZONE

Not Available

Back to Tip of the Day List Next Tip