Database Specific Date Functions
=====The following table contains some of the most common database-specific date functions: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
TN3905 (TN5200-7X0-0019): How to use pass-through expression- ApplySimple in MicroStrategy SQL Generation Engine 8.x
select distinct date_trunc('week', (a11.datetime)) AS Week,
('Week '|| WEEK_ISO(a11.datetime) || ' / ' || a11.year_sid) AS WeekDesciption
from mstr_datamart.date_dim a11
Comments
Post a Comment