The below information would be helpful for the end users to address if there are any queries related to DATE specific function usage in MicroStrategy
Database Specific Date Functions
The following table contains some of the most common database-specific date functions:
Database | ||||
Description | SQL Server | Oracle | DB2 UDB | Teradata |
Retrieve the database date |
getdate()
|
sysdate
|
current date
|
date
|
Convert a string to a date |
cast (‘2001/01/20’ as datetime)
|
To_Date(‘2002-01-20’, ‘YYYY-MM-DD’)
|
cast (’01/20/2002′ as date)
|
cast (‘2001/01/20’ as date)
|
Get the day from a date |
datepart(day, date_id)
|
to_number(to_char(date_id, ‘DD’))
|
day(date_id)
|
extract (day from date_id)
|
Get the month from a date |
datepart(month, date_id)
|
to_number(to_char(date_id, ‘MM’))
|
month(date_id)
|
extract (month from date_id)
|
Subtract one month to a date |
dateadd(month, -1, date_id)
|
add_months(date_id, -1)
|
date_id – 1 months
|
add_months(date_id, -1)
|
Subtract one day to a date |
dateadd(day, -1, date_id)
|
date_id -1
|
date_id – 1 days
|
cast(date_id as date) – 1
|
For more information on using these functions in the MicroStrategy’s architecture, refer to the MicroStrategy Product documentation and the following Microstrategy Knowledge Base document:
- TN3905 (TN5200-7X0-0019): How to use pass-through expression- ApplySimple in MicroStrategy SQL Generation Engine 8.x
Have a Happy Reading!!
Reference: MicroStrategy Knowledge Base