Date and Time Function

MicroStrategy pushes the calculations for the following list of built-in date and time functions down to PostgreSQL using the associated SQL patterns.

Function	SQL Pattern or Example
AddDays	(#0 + #1 * INTERVAL '1 DAY')
AddMonths	(#0 + #1 * INTERVAL '1 MONTH')
CurrentDate	CURRENT_DATE
CurrentDatetime	CURRENT_TIMESTAMP
CurrentTime	CURRENT_TIME
Date	CAST(#0 AS DATE)
DayOfMonth	EXTRACT(DAY FROM #0)
DayOfWeek	(EXTRACT(DOW FROM #0) + 1)
DayOfYear	EXTRACT(DOY FROM #0)
DaysBetween	(CAST(#1 AS DATE) - CAST(#0 AS DATE))
Hour	EXTRACT(HOUR FROM #0)
MilliSecond	(EXTRACT(MILLISECONDS FROM #0) % 1000) :: integer
Minute	EXTRACT(MINUTE FROM #0)
Month	EXTRACT(MONTH FROM #0)
MonthEndDate	(CAST(#0 AS DATE) + INTERVAL '1 MONTH' - EXTRACT(DAY FROM (#0 + INTERVAL '1 MONTH')) * INTERVAL '1 DAY')
MonthsBetween	(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))
MonthStartDate	(CAST(#0 AS DATE) - (EXTRACT(DAY FROM #0) - 1) * INTERVAL '1 DAY')
Quarter	EXTRACT(QUARTER FROM #0)
Second	CAST(BTRIM(CAST(EXTRACT(SECOND FROM #0)AS CHAR(2)),'.')AS INT)
Week	EXTRACT(WEEK FROM #0)
Year	EXTRACT(YEAR FROM #0)
YearEndDate	(CAST(#0 AS DATE) + (13 - EXTRACT(MONTH FROM #0)) * INTERVAL '1 MONTH' - EXTRACT(DAY FROM #0) * INTERVAL '1 DAY')
YearStartDate	(CAST(#0 AS DATE) - (EXTRACT(DAY FROM #0) - 1) * INTERVAL '1 DAY' - (EXTRACT(MONTH FROM #0) - 1) * INTERVAL '1 MONTH')

MicroStrategy use the analytical engine to implement date and time function below:

Function	Example of Analytical SQL calculated by the Analytical Engine
DateDiff	DateDiff(WJXBFS1, WJXBFS2, d)
FiscalWeek	FiscalWeek(WJXBFS1)
ToDateTime	ToDateTime(WJXBFS1)
FiscalMonth	FiscalMonth(WJXBFS1)
FiscalQuarter	FiscalMonth(WJXBFS1)
FiscalYear	FiscalMonth(WJXBFS1)
QuarterStartDate	QuarterStartDate(WJXBFS1)
WeekStartDate	WeekStartDate(WJXBFS1)