In this blog I am going to talk about a particular use case discovered in the Teradata SQL code we have analyzed and converted with SAP Advanced SQL Migration for 2 different customers. The source code developed by customers had datetime columns in tables or variables directly compared with numeric values what means Teradata performs implicit conversion from date to numeric and vice versa. This is an example of query showing this:
SELECT <col1>,<col2> … FROM <table_name> WHERE <colx> = 80991231
Where <colx> is defined as date data type.
Teradata documentation clearly stays that date values can be converted to numeric data types following a formula:
When a date is converted to a numeric, the value returned is the integer value for the internal stored date, which is encoded using the following formula:
(year - 1900) * 10000 + (month * 100) + day
Allowable date values range from AD January 1, 0001 to AD December 31, 9999.
Further details can be found in this link:
Teradata Date-to-Numeric Conversion
NOTE: Microsoft SQL Server also allows similar comparisons/conversions but semantic is very different, check this link to further details (
SQL Server date to numeric conversion).
This piece of SQL code shows how this stuff works in Teradata:
BTEQ -- Enter your SQL request or BTEQ command:
create table test_date (id int, d date, date_as_int int);
*** Table has been created.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command:
insert into test_date values (1, current_date, current_date);
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command:
insert into test_date values (2, current_date + 2, current_date + 2);
*** Insert completed. One row added.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request or BTEQ command:
select * from test_date;
*** Query completed. 2 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
id d date_as_int
----------- -------- -----------
1 19/09/18 1190918
2 19/09/20 1190920
BTEQ -- Enter your SQL request or BTEQ command:
select * from test_date where d < 1190919;
*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.
id d date_as_int
----------- -------- -----------
1 19/09/18 1190918
BTEQ -- Enter your SQL request or BTEQ command:
select * from test_date where d > 1190919;
*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.
id d date_as_int
----------- -------- -----------
2 19/09/20 1190920
SAP Advanced SQL Migration provides 2 RTCs (
Run Time Components), namely sp_f_dbmtk_convert_date_as_int & sp_f_dbmtk_convert_int_as_date, to perform this conversion and the tool is detecting (checking columns, tables variables and values) when an explicit conversion has to be done in Hana, check an example converting this Teradata query:
select * from test_date where d < 1190919
The conversion returned by SAP SQL Advanced Migration is:
SELECT /* ORIGSQL: select * from test_date where d < 1190919; */
*
FROM test_date
WHERE
d < sapdbmtk.sp_f_dbmtk_convert_int_as_date(1190919);
Running the converted query on Hana with a similar table and similar data we get the same output:
hdbsql SP4=> select * from test_date
> go
ID,D,DATE_AS_INT
1,2019-09-18,1190918
1,2019-09-20,1190920
2 rows selected (overall time 129,497 msec; server time 226 usec)
hdbsql SP4=> SELECT /* ORIGSQL: select * from test_date where d < 1190919 */
> *
> FROM
> test_date
> WHERE
> d < sp_f_dbmtk_convert_int_as_date(1190919);
> go
ID,D,DATE_AS_INT
1,2019-09-18,1190918
1 row selected (overall time 76,277 msec; server time 1735 usec)
hdbsql SP4=>
Other related posts:
