This requires Builder privileges for utilization.
| Function Returns | Syntax | Data Type | Results Example |
| Calendar Year | YEAR(datetime_column) | INT | 2015 |
| Calendar Quarter | DATEPART(q, datetime_column) | INT | 2 |
| Day of Year Number | DATEPART(dy, datetime_column) | INT | 365 |
| Month Number | DATEPART(m, datetime_column) | INT | 12 |
| Month Name | DATENAME(MONTH, datetime_column) | STRING | February |
| Short Month Name | LEFT(DATENAME(month, datetime_column),3) | STRING | Feb |
| First Day in Month | DATEADD(m, DATEDIFF(m, 0, datetime_columnn), 0) | DateTime | 2010-01-01 00:00:00.000 |
| ISO Week Number | DATEPART(ISOWK, datetime_column) | INT | 42 |
| Weekday Name | DATENAME(WEEKDAY, datetime_column) | STRING | Friday |
|
Short Weekday Name |
LEFT(DATENAME(WEEKDAY, datetime_column), 3) | STRING | Fri |
| Date | CAST(datetime_column as date) | DateTime | 2010-01-01 |
| Day in Week | DATEPART(WEEKDAY, datetime_column) | INT | 5 |
| Day in Month | DATEPART(d, datetime_column) | INT | 25 |
| Boolean Weekend | CASE DATEPART(WEEKDAY, datetime_column) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END | INT | 1 = True, 0 = False |
| Hour(s) in Day | DATEPART(HOUR, datetime_column) | INT | 13 |
| Minute(s) in Hour | DATEPART(MINUTE, datetime_column) | INT | 56 |
| Second(s) in Minute |
DATEPART(SECOND, datetime_column)
|
INT | 42 |
| Fiscal Quarter (July - June) |
CASE WHEN MONTH(datetime_column) BETWEEN 1 AND 3 THEN 'FY' + convert(char(4), YEAR(datetime_column) - 0) + '-' + 'Q3' WHEN MONTH(datetime_column) BETWEEN 4 AND 6 THEN 'FY' + convert(char(4), YEAR(datetime_column) - 0) + '-' + 'Q4' WHEN MONTH(datetime_column) BETWEEN 7 AND 9 THEN 'FY' + convert(char(4), YEAR(datetime_column) + 1) + '-' + 'Q1' WHEN MONTH(datetime_column) BETWEEN 10 AND 12 THEN 'FY' + convert(char(4), YEAR(datetime_column) + 1) + '-' + 'Q2' END |
STRING | FY2009-Q4 |
| Fiscal Quarter (October - September) |
CASE WHEN MONTH(datetime_column) BETWEEN 1 AND 3 THEN 'FY' + convert(char(4), YEAR(datetime_column) - 0) + '-' + 'Q2' WHEN MONTH(datetime_column) BETWEEN 4 AND 6 THEN 'FY' + convert(char(4), YEAR(datetime_column) - 0) + '-' + 'Q3' WHEN MONTH(datetime_column) BETWEEN 7 AND 9 THEN 'FY' + convert(char(4), YEAR(datetime_column) - 0) + '-' + 'Q4' WHEN MONTH(datetime_column) BETWEEN 10 AND 12 THEN 'FY' + convert(char(4), YEAR(datetime_column) + 1) + '-' + 'Q1' END |
STRING | FY2009-Q4 |
| Date Difference | DATEDIFF(DAY, datetime_column_2, datetime_column_1) | INT | 36 |
All queries in this guide are provided as-is for reference purposes. iDashboards Technical Support Team will not assist with authoring new queries. Please check out Microsoft's Date and Time Data Types and Functions documentation for more information on anything concerning Datetime.
Comments
0 comments
Please sign in to leave a comment.