Knowledge Base and Samples

Date Manipulation inside a "SqlQuery" Statement

This article provides detailed information about how to use dates inside a SqlQuery statement.

The SqlQuery statement lets you join and combine the resulting data from other statements by using SQL Select syntax. Enesys RS Data Extension uses SQLite SQL syntax. You can find detailed information about the date and time functions on the following web page.

This article provides samples and additional information in the context of Enesys RS Data Extension.

Date Comparison

For comparing dates inside a SqlQuery statement, you should use DATE or DATETIME functions to avoid any side effects.

Comparing dates without times

        SELECT *
FROM Orders
WHERE DATE(OrderDate) >= DATE('1998-05-05 05:05:05')
        
      

you can use a parameter (or some other field) instead of a literal:

        SELECT *
FROM Orders
WHERE DATE(OrderDate) >= DATE(@StartDate!)
        
      

Comparing dates including times

        SELECT *
FROM Orders
WHERE DATE(OrderDate) >= DATE('1998-05-05 05:05:05')

      

you can use a parameter (or some other field) instead of a literal:

        SELECT *
FROM Orders
WHERE DATETIME(OrderDate) >= DATETIME(@StartDate!)
        
      

Date Function Samples

In this chapter we will provide examples of most date functions available. In some cases, we will also provide the SQL Server counterpart.

Current Date

DATETIME('now')

GETUTCDATE()

DATETIME('now', 'localtime')

GETDATE()

Specific parts of a date

For the purpose of illustrating the following examples, we will consider that dateField has the following value: 2011-02-11 13:50:10.

STRFTIME('%Y', dateField)

YEAR(dateField)

(STRFTIME('%m', dateField) + 2) / 3

DATEPART(QUARTER, dateField)

STRFTIME('%m', dateField)

MONTH(dateField)

CASE STRFTIME('%m', dateField)
WHEN '01' THEN 'January' 
WHEN '02' THEN 'February'
WHEN '03' THEN 'March' 
WHEN '04' THEN 'April' 
WHEN '05' THEN 'May' 
WHEN '06' THEN 'June'
WHEN '07' THEN 'July' 
WHEN '08' THEN 'August'
WHEN '09' THEN 'September' 
WHEN '10' THEN 'October'
WHEN '11' THEN 'November' 
ELSE 'December' END

DATENAME(MONTH, dateField)

STRFTIME('%j', dateField)

DATEPART(DAYOFYEAR, dateField)

STRFTIME('%d', dateField)

DAY(dateField)

STRFTIME('%W', dateField) + 1

DATEPART(WEEK, dateField)

CASE STRFTIME('%w', dateField)
WHEN '1' THEN 'Monday'
WHEN '2' THEN 'Tuesday'
WHEN '3' THEN 'Wednesday'
WHEN '4' THEN 'Thursday'
WHEN '5' THEN 'Friday'
WHEN '6' THEN 'Saturday'
ELSE 'Sunday' END

DATENAME(WEEKDAY, dateField)

STRFTIME('%H', dateField)

DATEPART(HOUR, dateField)

STRFTIME('%M', dateField)

DATEPART(MINUTE, dateField)

STRFTIME('%S', dateField)

DATEPART(SECOND, dateField)

You can also combine SQLite format strings:

STRFTIME('%m%Y', dateField)

STRFTIME('%m', dateField) || RIGHTSTR(STRFTIME('%Y', dateField), 2)

STRFTIME('%m-%Y', dateField)

STRFTIME('%Y%m%d', dateField)

It should be noted that the STRFTIME function always return a string unlike the SQL Server counter part that return a numeric value. Therefore, if you would like the same behavior as the SQL Server counterpart, and for comparison purposes, you may want to convert the result to an int by using the CAST function as shown below:

        SELECT * FROM Foo WHERE CAST(STRFTIME('%Y', dateField) AS INT) = 2011
      

In that case, comparing with a string would also work:

        SELECT * FROM Foo WHERE STRFTIME('%Y', dateField) = '2011'
      

Difference between two dates

We have to admit SQLite approach is not extremely natural. It is shown here for the sake of completeness. However, unless you need it, we would rather recomment using a DATEDIFF function directly within a Reporting Services expression.

CAST((JULIANDAY(endDate) – JULIANDAY(startDate)) AS INT)

DATEDIFF(DAY, startdate, enddate)

To get the number of hours or minutes, just multiply the result by 24 or 1440.

Adding or Removing Time

DATETIME(dateField, '+1 years')

DATETIME(dateField, '+2 months')

DATETIME(dateField, '-10 days')

DATETIME(dateField, '+5 hours')

DATETIME(dateField, '+30 minutes')

Date Shifting

Finally, SQLite syntax provides various modifiers that allow you to shift the date to some specific point in time like the start of the year. Note that you can use multiple modifiers for powerful date shifting. The following examples will certainly clarify:

DATETIME(dateField, 'start of year')

DATETIME(dateField, 'start of month')

DATETIME(dateField, 'start of day')

DATETIME(dateField, 'weekday 5')

DATETIME(dateField, 'start of month', 'weekday 0')

DATETIME(dateField, '+1 month', 'start of month')