T-SQL Date Expressions
Date expressions and functions supported by T-SQL / Microsoft SQL Server.
Date Expressions and Functions
Last 7 days:
DATEADD(DAY, - 7, GETDATE())One calendar month ago:
DATEADD(MONTH, - 1, GETDATE())Last day of of the prior calendar month:
EOMONTH(GETDATE(), - 1)This calendar month:
DATEADD(DAY, 1, EOMONTH(GETDATE(), - 1))Last two calendar months:
DATEADD(DAY, 1, EOMONTH(GETDATE(), - 2))This calendar year:
DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0)Last two calendar years:
DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) - 1, 0)
For more see SQL Date Manipulations: CONVERT, DATEADD, EOMonth & More.
Fiscal Period Expressions
Using fiscal period fields (YYYYPP) where
SchemaName is the entity name of the ETL+ entity where this expression is used.
Beginning of this year:
SELECT [FY_NoInt4]*100 FROM [SchemaName].[4_Today]Beginning of last year:
SELECT [FY_NoInt4]*100-1 FROM [SchemaName].[4_Today]From X periods ago (assuming 12 periods/yr. For 13per/yr, replace 88 with 87):
SELECT FP_YYYYPP_NoInt6 - CASE WHEN [FP_NoInt2]>X THEN X ELSE (88+X) END FROM [SchemaName].[4_Today]
NOTES
The SQL in ETL+'s Extract SQL Statement panel is not necessarily T-SQL complaint!
The SQL statements and syntax used in the Extract SQL Statement panel must work with the SQL syntax supported by the ETL+ Source Driver used to connect to the associated Source Object.
Related Pages
T-SQL date functions used in ETL+ Load Replace, ETL+ Design Page
Used in ETL+ Extract SQL Statement Panel when the data source is a MS SQL Server database/DBMS.