Breadcrumbs

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.