Cognos Analytics

​
When using the current_date function in a Cognos report, does it get the date from the Cognos Server or the source database server?
#CognosAnalyticswithWatson

current_date will get the value from the source database server.
If you need to have the one from the Cognos Server, you can use a macro expression:
# timestampMask ($current_timestamp, 'yyyy-mm-dd')#
It will be evaluated on the Cognos Server during query planning.

In Report Studio you can view the Cognos SQL and Native SQL when using Validate Options... ->set to Information and then do a Validate Report (these commands are available from the settings menu in Report Studio).

//Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Fri May 03, 2019 11:05 AM
From: Richard Mabjish
Subject: current_date function

​
When using the current_date function in a Cognos report, does it get the date from the Cognos Server or the source database server?
#CognosAnalyticswithWatson

​Hi Henk,

Thank you for your reply.

I've just found this link which basically says that CURRENT_DATE, CURRENT_TIME and also CURRENT_TIMESTAMPall three return date/time of the machine where the database is running. (see below)

Do you know another way to get the date from the Cognos server as opposed the database server?

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - IBM website
Original Message:
Sent: Fri May 03, 2019 01:24 PM
From: HENK CAZEMIER
Subject: current_date function

current_date will get the value from the source database server.
If you need to have the one from the Cognos Server, you can use a macro expression:
# timestampMask ($current_timestamp, 'yyyy-mm-dd')#
It will be evaluated on the Cognos Server during query planning.

In Report Studio you can view the Cognos SQL and Native SQL when using Validate Options... ->set to Information and then do a Validate Report (these commands are available from the settings menu in Report Studio).

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 11:05 AM
From: Richard Mabjish
Subject: current_date function

​
When using the current_date function in a Cognos report, does it get the date from the Cognos Server or the source database server?
#CognosAnalyticswithWatson

The macro method is the only supported way,  that will give you the date/time info from the Cognos Server.
There are a number of macro functions that help with manipulating the date/time info.
All these functions are available in Framework Manager, Report Studio, Webbased Modeling, i.e. anywhere you can enter an expression in Cognos.
Have you  tried the macro in the previous message?
//Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Fri May 03, 2019 02:58 PM
From: Richard Mabjish
Subject: current_date function

​Hi Henk,

Thank you for your reply.

I've just found this link which basically says that CURRENT_DATE, CURRENT_TIME and also CURRENT_TIMESTAMPall three return date/time of the machine where the database is running. (see below)

Do you know another way to get the date from the Cognos server as opposed the database server?

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - IBM website
Original Message:
Sent: Fri May 03, 2019 01:24 PM
From: HENK CAZEMIER
Subject: current_date function

current_date will get the value from the source database server.
If you need to have the one from the Cognos Server, you can use a macro expression:
# timestampMask ($current_timestamp, 'yyyy-mm-dd')#
It will be evaluated on the Cognos Server during query planning.

In Report Studio you can view the Cognos SQL and Native SQL when using Validate Options... ->set to Information and then do a Validate Report (these commands are available from the settings menu in Report Studio).

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 11:05 AM
From: Richard Mabjish
Subject: current_date function

​
When using the current_date function in a Cognos report, does it get the date from the Cognos Server or the source database server?
#CognosAnalyticswithWatson

Hi Henk,

The macro is based on the current_timestamp​ function, which according to the provided link is getting the date from the DB server.

Can you please elaborate how this macro could get me the date from the Cognos Server?
Original Message:
Sent: Fri May 03, 2019 03:39 PM
From: HENK CAZEMIER
Subject: current_date function

The macro method is the only supported way,  that will give you the date/time info from the Cognos Server.
There are a number of macro functions that help with manipulating the date/time info.
All these functions are available in Framework Manager, Report Studio, Webbased Modeling, i.e. anywhere you can enter an expression in Cognos.
Have you  tried the macro in the previous message?
//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 02:58 PM
From: Richard Mabjish
Subject: current_date function

​Hi Henk,

Thank you for your reply.

I've just found this link which basically says that CURRENT_DATE, CURRENT_TIME and also CURRENT_TIMESTAMPall three return date/time of the machine where the database is running. (see below)

Do you know another way to get the date from the Cognos server as opposed the database server?

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - IBM website
Original Message:
Sent: Fri May 03, 2019 01:24 PM
From: HENK CAZEMIER
Subject: current_date function

current_date will get the value from the source database server.
If you need to have the one from the Cognos Server, you can use a macro expression:
# timestampMask ($current_timestamp, 'yyyy-mm-dd')#
It will be evaluated on the Cognos Server during query planning.

In Report Studio you can view the Cognos SQL and Native SQL when using Validate Options... ->set to Information and then do a Validate Report (these commands are available from the settings menu in Report Studio).

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 11:05 AM
From: Richard Mabjish
Subject: current_date function

​
When using the current_date function in a Cognos report, does it get the date from the Cognos Server or the source database server?
#CognosAnalyticswithWatson

I see where the confusion comes in. There is a 'function' called current_date and current_timestamp, but the one in the macro expression is a session parameter.
All macros are evaluated at query planning time.
The $current_timestamp is a session parameter, it is not a function call that can be pushed to the back-end database.
Here is how it shows in the Framework Manager UI

When you use the macro and look at the generated Cognos SQL you'd it show up as a literal, something like:

SELECT
         ORDER_HEADER0.RETAILER_NAME AS Retailer_Name,
         DATE '2019-05-03' AS Data_Item2
FROM
         gosales_8_2.gosales_8_2.gosl.ORDER_HEADER ORDER_HEADER0
GROUP BY
       ORDER_HEADER0.RETAILER_NAME,
       DATE '2019-05-03'

//Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Fri May 03, 2019 03:42 PM
From: Richard Mabjish
Subject: current_date function

Hi Henk,

The macro is based on the current_timestamp​ function, which according to the provided link is getting the date from the DB server.

Can you please elaborate how this macro could get me the date from the Cognos Server?
Original Message:
Sent: Fri May 03, 2019 03:39 PM
From: HENK CAZEMIER
Subject: current_date function

The macro method is the only supported way,  that will give you the date/time info from the Cognos Server.
There are a number of macro functions that help with manipulating the date/time info.
All these functions are available in Framework Manager, Report Studio, Webbased Modeling, i.e. anywhere you can enter an expression in Cognos.
Have you  tried the macro in the previous message?
//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 02:58 PM
From: Richard Mabjish
Subject: current_date function

​Hi Henk,

Thank you for your reply.

I've just found this link which basically says that CURRENT_DATE, CURRENT_TIME and also CURRENT_TIMESTAMPall three return date/time of the machine where the database is running. (see below)

Do you know another way to get the date from the Cognos server as opposed the database server?

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - IBM website
Original Message:
Sent: Fri May 03, 2019 01:24 PM
From: HENK CAZEMIER
Subject: current_date function

current_date will get the value from the source database server.
If you need to have the one from the Cognos Server, you can use a macro expression:
# timestampMask ($current_timestamp, 'yyyy-mm-dd')#
It will be evaluated on the Cognos Server during query planning.

In Report Studio you can view the Cognos SQL and Native SQL when using Validate Options... ->set to Information and then do a Validate Report (these commands are available from the settings menu in Report Studio).

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 11:05 AM
From: Richard Mabjish
Subject: current_date function

​
When using the current_date function in a Cognos report, does it get the date from the Cognos Server or the source database server?
#CognosAnalyticswithWatson

Thank Henk,

Do you know what would cause current_date to return the wrong date? May 3 will return May 1!?

(the date on the DB server is correct)

Original Message:
Sent: Fri May 03, 2019 04:49 PM
From: HENK CAZEMIER
Subject: current_date function

I see where the confusion comes in. There is a 'function' called current_date and current_timestamp, but the one in the macro expression is a session parameter.
All macros are evaluated at query planning time.
The $current_timestamp is a session parameter, it is not a function call that can be pushed to the back-end database.
Here is how it shows in the Framework Manager UI

When you use the macro and look at the generated Cognos SQL you'd it show up as a literal, something like:

SELECT
         ORDER_HEADER0.RETAILER_NAME AS Retailer_Name,
         DATE '2019-05-03' AS Data_Item2
FROM
         gosales_8_2.gosales_8_2.gosl.ORDER_HEADER ORDER_HEADER0
GROUP BY
       ORDER_HEADER0.RETAILER_NAME,
       DATE '2019-05-03'

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 03:42 PM
From: Richard Mabjish
Subject: current_date function

Hi Henk,

The macro is based on the current_timestamp​ function, which according to the provided link is getting the date from the DB server.

Can you please elaborate how this macro could get me the date from the Cognos Server?
Original Message:
Sent: Fri May 03, 2019 03:39 PM
From: HENK CAZEMIER
Subject: current_date function

The macro method is the only supported way,  that will give you the date/time info from the Cognos Server.
There are a number of macro functions that help with manipulating the date/time info.
All these functions are available in Framework Manager, Report Studio, Webbased Modeling, i.e. anywhere you can enter an expression in Cognos.
Have you  tried the macro in the previous message?
//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 02:58 PM
From: Richard Mabjish
Subject: current_date function

​Hi Henk,

Thank you for your reply.

I've just found this link which basically says that CURRENT_DATE, CURRENT_TIME and also CURRENT_TIMESTAMPall three return date/time of the machine where the database is running. (see below)

Do you know another way to get the date from the Cognos server as opposed the database server?

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - IBM website
Original Message:
Sent: Fri May 03, 2019 01:24 PM
From: HENK CAZEMIER
Subject: current_date function

current_date will get the value from the source database server.
If you need to have the one from the Cognos Server, you can use a macro expression:
# timestampMask ($current_timestamp, 'yyyy-mm-dd')#
It will be evaluated on the Cognos Server during query planning.

In Report Studio you can view the Cognos SQL and Native SQL when using Validate Options... ->set to Information and then do a Validate Report (these commands are available from the settings menu in Report Studio).

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 11:05 AM
From: Richard Mabjish
Subject: current_date function

​
When using the current_date function in a Cognos report, does it get the date from the Cognos Server or the source database server?
#CognosAnalyticswithWatson

Hi Richard,
I've not seen that issue before.
Would you be able to show me on a webex ?
//Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Fri May 03, 2019 09:48 PM
From: Richard Mabjish
Subject: current_date function


Thank Henk,

Do you know what would cause current_date to return the wrong date? May 3 will return May 1!?

(the date on the DB server is correct)

Original Message:
Sent: Fri May 03, 2019 04:49 PM
From: HENK CAZEMIER
Subject: current_date function

I see where the confusion comes in. There is a 'function' called current_date and current_timestamp, but the one in the macro expression is a session parameter.
All macros are evaluated at query planning time.
The $current_timestamp is a session parameter, it is not a function call that can be pushed to the back-end database.
Here is how it shows in the Framework Manager UI

When you use the macro and look at the generated Cognos SQL you'd it show up as a literal, something like:

SELECT
         ORDER_HEADER0.RETAILER_NAME AS Retailer_Name,
         DATE '2019-05-03' AS Data_Item2
FROM
         gosales_8_2.gosales_8_2.gosl.ORDER_HEADER ORDER_HEADER0
GROUP BY
       ORDER_HEADER0.RETAILER_NAME,
       DATE '2019-05-03'

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 03:42 PM
From: Richard Mabjish
Subject: current_date function

Hi Henk,

The macro is based on the current_timestamp​ function, which according to the provided link is getting the date from the DB server.

Can you please elaborate how this macro could get me the date from the Cognos Server?
Original Message:
Sent: Fri May 03, 2019 03:39 PM
From: HENK CAZEMIER
Subject: current_date function

The macro method is the only supported way,  that will give you the date/time info from the Cognos Server.
There are a number of macro functions that help with manipulating the date/time info.
All these functions are available in Framework Manager, Report Studio, Webbased Modeling, i.e. anywhere you can enter an expression in Cognos.
Have you  tried the macro in the previous message?
//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 02:58 PM
From: Richard Mabjish
Subject: current_date function

​Hi Henk,

Thank you for your reply.

I've just found this link which basically says that CURRENT_DATE, CURRENT_TIME and also CURRENT_TIMESTAMPall three return date/time of the machine where the database is running. (see below)

Do you know another way to get the date from the Cognos server as opposed the database server?

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - IBM website
Original Message:
Sent: Fri May 03, 2019 01:24 PM
From: HENK CAZEMIER
Subject: current_date function

current_date will get the value from the source database server.
If you need to have the one from the Cognos Server, you can use a macro expression:
# timestampMask ($current_timestamp, 'yyyy-mm-dd')#
It will be evaluated on the Cognos Server during query planning.

In Report Studio you can view the Cognos SQL and Native SQL when using Validate Options... ->set to Information and then do a Validate Report (these commands are available from the settings menu in Report Studio).

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 11:05 AM
From: Richard Mabjish
Subject: current_date function

​
When using the current_date function in a Cognos report, does it get the date from the Cognos Server or the source database server?
#CognosAnalyticswithWatson

As of 11.2.4, you can use the following macro to pull the current date from the Cognos Analytics servers. This is for Dynamic Query Mode.

# $current_date #

------------------------------
Armin Kamal
------------------------------

Original Message:
Sent: Fri May 03, 2019 11:23 PM
From: HENK CAZEMIER
Subject: current_date function

Hi Richard,
I've not seen that issue before.
Would you be able to show me on a webex ?
//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 09:48 PM
From: Richard Mabjish
Subject: current_date function


Thank Henk,

Do you know what would cause current_date to return the wrong date? May 3 will return May 1!?

(the date on the DB server is correct)

Original Message:
Sent: Fri May 03, 2019 04:49 PM
From: HENK CAZEMIER
Subject: current_date function

I see where the confusion comes in. There is a 'function' called current_date and current_timestamp, but the one in the macro expression is a session parameter.
All macros are evaluated at query planning time.
The $current_timestamp is a session parameter, it is not a function call that can be pushed to the back-end database.
Here is how it shows in the Framework Manager UI

When you use the macro and look at the generated Cognos SQL you'd it show up as a literal, something like:

SELECT
         ORDER_HEADER0.RETAILER_NAME AS Retailer_Name,
         DATE '2019-05-03' AS Data_Item2
FROM
         gosales_8_2.gosales_8_2.gosl.ORDER_HEADER ORDER_HEADER0
GROUP BY
       ORDER_HEADER0.RETAILER_NAME,
       DATE '2019-05-03'

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 03:42 PM
From: Richard Mabjish
Subject: current_date function

Hi Henk,

The macro is based on the current_timestamp​ function, which according to the provided link is getting the date from the DB server.

Can you please elaborate how this macro could get me the date from the Cognos Server?
Original Message:
Sent: Fri May 03, 2019 03:39 PM
From: HENK CAZEMIER
Subject: current_date function

The macro method is the only supported way,  that will give you the date/time info from the Cognos Server.
There are a number of macro functions that help with manipulating the date/time info.
All these functions are available in Framework Manager, Report Studio, Webbased Modeling, i.e. anywhere you can enter an expression in Cognos.
Have you  tried the macro in the previous message?
//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 02:58 PM
From: Richard Mabjish
Subject: current_date function

​Hi Henk,

Thank you for your reply.

I've just found this link which basically says that CURRENT_DATE, CURRENT_TIME and also CURRENT_TIMESTAMPall three return date/time of the machine where the database is running. (see below)

Do you know another way to get the date from the Cognos server as opposed the database server?

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - IBM website
Original Message:
Sent: Fri May 03, 2019 01:24 PM
From: HENK CAZEMIER
Subject: current_date function

current_date will get the value from the source database server.
If you need to have the one from the Cognos Server, you can use a macro expression:
# timestampMask ($current_timestamp, 'yyyy-mm-dd')#
It will be evaluated on the Cognos Server during query planning.

In Report Studio you can view the Cognos SQL and Native SQL when using Validate Options... ->set to Information and then do a Validate Report (these commands are available from the settings menu in Report Studio).

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 11:05 AM
From: Richard Mabjish
Subject: current_date function

​
When using the current_date function in a Cognos report, does it get the date from the Cognos Server or the source database server?
#CognosAnalyticswithWatson

Hi Richard,

Can you check the Native SQL expression for your Cognos query CURRENT_DATE item?

E.g. for SQL Server:   Cognos function CURRENT_DATE => SQL Server expression  CAST(CURRENT_TIMESTAMP AS DATE)

If you run a simple SQL with this expression do you get the correct date?

------------------------------
Andrei Istomine
Open to work - anything Cognos
https://www.linkedin.com/in/andreii/
------------------------------

Original Message:
Sent: Fri May 03, 2019 09:48 PM
From: Richard Mabjish
Subject: current_date function


Thank Henk,

Do you know what would cause current_date to return the wrong date? May 3 will return May 1!?

(the date on the DB server is correct)

Original Message:
Sent: Fri May 03, 2019 04:49 PM
From: HENK CAZEMIER
Subject: current_date function

I see where the confusion comes in. There is a 'function' called current_date and current_timestamp, but the one in the macro expression is a session parameter.
All macros are evaluated at query planning time.
The $current_timestamp is a session parameter, it is not a function call that can be pushed to the back-end database.
Here is how it shows in the Framework Manager UI

When you use the macro and look at the generated Cognos SQL you'd it show up as a literal, something like:

SELECT
         ORDER_HEADER0.RETAILER_NAME AS Retailer_Name,
         DATE '2019-05-03' AS Data_Item2
FROM
         gosales_8_2.gosales_8_2.gosl.ORDER_HEADER ORDER_HEADER0
GROUP BY
       ORDER_HEADER0.RETAILER_NAME,
       DATE '2019-05-03'

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 03:42 PM
From: Richard Mabjish
Subject: current_date function

Hi Henk,

The macro is based on the current_timestamp​ function, which according to the provided link is getting the date from the DB server.

Can you please elaborate how this macro could get me the date from the Cognos Server?
Original Message:
Sent: Fri May 03, 2019 03:39 PM
From: HENK CAZEMIER
Subject: current_date function

The macro method is the only supported way,  that will give you the date/time info from the Cognos Server.
There are a number of macro functions that help with manipulating the date/time info.
All these functions are available in Framework Manager, Report Studio, Webbased Modeling, i.e. anywhere you can enter an expression in Cognos.
Have you  tried the macro in the previous message?
//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 02:58 PM
From: Richard Mabjish
Subject: current_date function

​Hi Henk,

Thank you for your reply.

I've just found this link which basically says that CURRENT_DATE, CURRENT_TIME and also CURRENT_TIMESTAMPall three return date/time of the machine where the database is running. (see below)

Do you know another way to get the date from the Cognos server as opposed the database server?

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - IBM website
Original Message:
Sent: Fri May 03, 2019 01:24 PM
From: HENK CAZEMIER
Subject: current_date function

current_date will get the value from the source database server.
If you need to have the one from the Cognos Server, you can use a macro expression:
# timestampMask ($current_timestamp, 'yyyy-mm-dd')#
It will be evaluated on the Cognos Server during query planning.

In Report Studio you can view the Cognos SQL and Native SQL when using Validate Options... ->set to Information and then do a Validate Report (these commands are available from the settings menu in Report Studio).

//Henk

------------------------------
HENK CAZEMIER

Original Message:
Sent: Fri May 03, 2019 11:05 AM
From: Richard Mabjish
Subject: current_date function

​
When using the current_date function in a Cognos report, does it get the date from the Cognos Server or the source database server?
#CognosAnalyticswithWatson