T-SQL Date Expressions

Owned by Cortlandt _
Last updated: Mar 28, 2024
1 min read

Date and time data types and functions (Transact-SQL)

Date Expressions

  • 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)

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

ETL+'s Extract SQL Statement panel is not necessarily T-SQL complaint!
See