Updated March 4, 2023
Introduction to Teradata Current Date
Teradata’s CURRENT_DATE returns the current date at the time of submission of the query. If in case the CURRENT_DATE is invoked more than once, then the same date is returned the times CURRENT_DATE is being invoked. The date returned does not change during the entire duration when the request is being made. By default, the CURRENT_DATE returns the local timezone’s timestamp. Anyhow, we can specify the displacements if in case required. Moreover, the TimeDateWZControl flag also plays an important role. We tend to get different results depending upon this flag is set to enabled or disabled.
What is Teradata’s Current Date?
CURRENT_DATE is Teradata’s date function that is ANSI compatible as well. Primarily it returns the current date at that instance, but there are several alternatives that one can opt for as per the requirements. There can be multiple elements that can be used in combination with the CURRENT_DATE to fetch the date’s requirements.
- AT LOCAL: It makes sure that the value returned by the CURRENT_DATE function is fetched using the session timestamp & session timezone, Provided that the DBS control flag TimeDateWZControl is set to true.
- Otherwise, if the TimeDateWZControl is set to false, disabled, then the CURRENT_DATE returns the timestamp based on the Teradata DB server & the session timezone.
- AT [TIME ZONE] expression: This makes sure that the timezone is displaced by the duration that is specified by the expression before returning the value of the function CURRENT_DATE
- The data type of the expression is defined in INTERVAL HOUR(2) TO MINUTE.
- AT [TIME ZONE] time_zone_string: Here, the time_zone_string acts as a reference timestamp to determine the displacement of the data returned by the CURRENT_DATE
Both AT clause and the CURRENT_DATE are ANSI Compatible.
If we specify the CURRENT_DATE either without the AT clause like below:
CURRENT_DATEor as
CURRENT_DATE AT LOCALThen the value returned in both of the above-mentioned scenarios is dependent on the settings of the DBS control flag, which is:
- TimeDateWZControl
Case 1: If this flag is enabled or set to true, then the CURRENT_DATE returns the date as per session time and session time zone.
Case 2: If this flag is disabled or set to false, then the CURRENT_DATE returns the date as per Teradata’s Local DB time value and the session time zone.
CURRENT_DATE is also adjusted as per the start and end of the Daylight time savings.
The resultant data type, format and the title for the CURRENT_DATE are:
- The data type is a date,
- Format: When the date form mode is set, the default format is IntegerDate.
- Default Title is Date; otherwise, we can also set the user-defined titles using the as clause as CURRENT_DATE as Today_date,
- The default title that is Date will be replaced by the title ‘Today’,
How to See CURRENT_DATE in Teradata?
Let’s take an example to understand it in detail:
SELECT CURRENT_DATE as curr_Date
This will return the date at the time of execution and in the default format that is the date (data type) – YYYY-MM-DD.
What if we want to add days to the current date and want the same to be returned?
The SQL statement required for this can be as below:
SELECT CURRENT_DATE + 1
This will add a day to the current date returned by the system. Suppose if the current_date in the system is ‘2020-03-24’. Then the above SQL that has an addition of one day will return ‘2020-03-25’.
Let’s have the same using method 2, which is having slightly different syntax, as mentioned below:
SELECT CURRENT_DATE + INTERVAL '15' DAY
This will add 15 days to the current date returned by the system. Suppose if the current_date in the system is ‘2020-03-24’. Then the above SQL that has an addition of one day will return ‘2020-04-08.’
What if we want to subtract days to the current date and want the same to be returned?
The SQL statement required for this can be as below:
SELECT CURRENT_DATE - 1
This will subtract a day to the current date returned by the system. Suppose if the current_date in the system is ‘2020-03-24’. Then the above SQL that has an addition of one day will return ‘2020-03-23.’
Let’s have the same using method 2, which is having slightly different syntax, as mentioned below:
SELECT CURRENT_DATE - INTERVAL '15' DAY
This will subtract 15 days to the current date returned by the system. Suppose if the current_date in the system is ‘2020-03-24’. Then the above SQL that has an addition of one day will return ‘2020-03-09.’
Examples of Teradata Current Date
Let’s take an example to find out the current date’s practical usage and understand the same. Consider the below table wherein we have the Order id and order date. Let’s assume we need to find out off of the orders made in the past 30 days.
Let’s discuss the approach we can follow to achieve this over here:
We can compare the order date with (CURRENT_DATE – 15 ), and if the order date is greater than that, then we can set the derived field as True else false. This way, we can identify all of the orders that have been made in the past 30 days.
Let’s write an SQL statement for the same.
SELECT ID, ORDER_DATE,
CASE
WHEN ORDER_DATE >= (CURRENT_DATE - 30) Then 'TRUE
ELSE 'FALSE
END as Indicator
from Table_nameThe new field indicator will be created over here, having the value as True if that order has been placed in the past 30 days. Suppose we take up the current date as ‘2020-03-20’ in this example, then the Indicator field will have the below values.
Using the indicator field, we can fetch only those records that have the orders placed from the past 30 days.
Conclusion
- CURRENT_DATE returns the current system date by default
- AT clause can be utilized with CURRENT_DATE to have a deviated date as per the requirements
- Both CURRENT_DATE & AT Clause are ANSI compatible.
- We can also add and subtract the required interval of days from the CURRENT_DATE as per the requirements.
Recommended Articles
We hope that this EDUCBA information on “Teradata Current Date” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
