Date functions specific to different databases

 

Date functions speccific to different databases

 Edit 0 3…

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

Also Custom Week function can be written in SQL for Vertica as :
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