NOW and NOWDATE

BQL provides functions that return the current date and time as a datetime data type. These are very helpful for expressions.

NOW Function

NOW returns the current date and time. The format of the return value is:

yyyy-mm-dd hh:mm:ss

For example, using the following query in Query Admin:

SELECT [Time.Month of Year] FROM [ALL] WHERE [Time.Date] = NOW

Birst generates the following:

SELECT DW_DM_TIME_DAY0_.Month_of_Year$ AS 'Month of Year'
FROM dbo.DW_DM_TIME_DAY DW_DM_TIME_DAY0_
WHERE DW_DM_TIME_DAY0_.Date$='2016-04-19 15:24:25'
GROUP BY DW_DM_TIME_DAY0_.Month_of_Year$

NOWDATE Function

NOWDATE returns the current date at midnight. The format of the return value is:

yyyy-mm-dd 12:00:00

For example, using the following query in Query Admin:

SELECT [Time.Month of Year] FROM [ALL] WHERE [Time.Date] = NOWDATE

Birst generates the following:

SELECT DW_DM_TIME_DAY0_.Month_of_Year$ AS 'Month of Year'
FROM dbo.DW_DM_TIME_DAY DW_DM_TIME_DAY0_
WHERE DW_DM_TIME_DAY0_.Date$='2016-04-19 12:00:00'
GROUP BY DW_DM_TIME_DAY0_.Month_of_Year$

Formatting and Casting

Use the FORMAT function to control the formatting of the datetime data. FORMAT returns a varchar, so you can also use it for simple casting.

FORMAT(NOWDATE, 'yyyy-MM-dd'))

See FORMAT Function.

Alternative Methods for Filtering on a Current Date

An alternative to using NOW and NOWDATE is to use the built-in time series "Ago" functions to filter on a current date. Filtering for a zero value returns the current date.

For example, to get the current month:

SELECT [Time.Month of Year] FROM [All] WHERE [Time.Months Ago]=0

To get the current date:

SELECT [Time.Date] FROM [ALL] WHERE [Time.Days Ago]=0