Technical Articles
SAP Advanced SQL Migration. SQL Server date to numeric conversion
In a parallel blog we have already seen some particular things about date/numeric conversion in Teradata (Teradata date to numeric conversion), in that blog I pointed out to Microsoft SQL Server implementing also a similar conversion in some way for those data types but with a very different semantic and also with some limitations. The purpose of this blog is describe this stuff in SQL Server and show how SAP Advanced SQL Migration is converting SQL code coming from SQL Server having this feature.
Some important differences with Teradata:
- The sematic in SQL Ser is very different, numeric value 0 means 1900-01-01 when converted to a datetime, numeric value 1 is 1900-01-02 …etc. Notice negative number means days back from 1900-01-01 and that decimal positions are allowed being .5 half of the day (12:00 PM):
1> select convert (datetime, 0.0)
2> go
-----------------------
1900-01-01 00:00:00.000
(1 rows affected)
1> select convert (datetime, -1.0)
2> go
-----------------------
1899-12-31 00:00:00.000
(1 rows affected)
1> select convert (datetime, 1.0)
2> go
-----------------------
1900-01-02 00:00:00.000
(1 rows affected)
1> select convert (datetime, 1.7)
2> go
-----------------------
1900-01-02 16:48:00.000
(1 rows affected)
1>
- While Teradata is able to the the convert implicitly in SQL Server that is not happening for all situations, depending on the data types the conversion can be implicit, explicit or even not allowed in some cases. For details check SQL Server manuals, section “Implicit conversions” under CAST and CONVERT (Transact-SQL). Examples:
· Conversion from int/float/real to datetime/smalldatetime is implicit:
1> create table test (id int, d datetime) 2> go 1> insert into test values (1, 0) 2> insert into test values (2, 10.5) 3> go (2 rows affected) 1> select * from test 2> go id d ----------- ----------------------- 1 1900-01-01 00:00:00.000 2 1900-01-11 12:00:00.000 (2 rows affected)
· Conversion from datetime/smalldatetime to int/float/real is explicit.
1> create table test (id int, float_date float) 2> go 1> insert into test values (1, getdate()) 2> go Msg 257, Level 16, State 3, Server JMARTIN2-W2K8VM\MSSQL2012, Line 1. Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query. 1> insert into test values (1, cast (getdate() as float)) 2> go (1 rows affected) 1> select * from test 2> go id float_date ----------- ------------------------ 1 43726.547134375003 (1 rows affected) 1>
· Conversion from date/time to int/float/real in not allowed.
1> create table test (id int, d date) 2> go 1> insert into test values (1, 10) 2> go Msg 206, Level 16, State 2, Server JMARTIN2-W2K8VM\MSSQL2012, Line 1. Operand type clash: int is incompatible with date 1>
SAP Advanced SQL Migration is detecting when this is happening in SQL Server (checking columns, tables variables and values) and applying the corresponding conversion in this case using expression to be able to be consistent with SQL Server behavior. For example this query is converting numeric value 100.5 to datetime:
1> select convert(datetime, 100.5) 2> go ----------------------- 1900-04-11 12:00:00.000 (1 rows affected) 1>
The conversion is this other one query in Hana returning the same result:
hdbsql SP4=> SELECT /* ORIGSQL: select convert(datetime, 100.5) */
> CAST(ADD_SECONDS('1900/01/01 00:00:00.000',(100.5*86400)) AS TIMESTAMP)
> FROM
> SYS.DUMMY;
> go
CAST(ADD_SECONDS('1900/01/01 00:00:00.000',(100.5*86400)) AS TIMESTAMP)
1900-04-11 12:00:00.000000000
Another example with this query converting 2 dates to integers, notice that in first one time is < 12:00 PM and the second is > 12:00 PM:
1> select convert(int, convert (datetime,'2019-09-19 10:25:50.577')), 2> convert(int, convert (datetime,'2019-09-19 12:25:50.577')) 3> go ----------- ----------- 43725 43726 (1 rows affected) 1>
The conversion is this returning same output:
hdbsql SP4=> SELECT /* ORIGSQL: select convert(int, convert (datetime,'2019-09-19 10:25:50.577')),
> convert(int, (...) */
> CAST(ROUND(SECONDS_BETWEEN('1900/01/01 00:00:00.000',
> CAST('2019-09-19 10:25:50.577' AS TIMESTAMP))/86400) AS INT),
> CAST(ROUND(SECONDS_BETWEEN('1900/01/01 00:00:00.000',
> CAST('2019-09-19 12:25:50.577' AS TIMESTAMP))/86400) AS INT)
> FROM
> SYS.DUMMY;
> go
CAST(ROUND(SECONDS_BETWEEN('1900/01/01 00:00:00.000',CAST('2019-09-19 10:25:50.577' AS TIMESTAMP))/86400) AS INTEGER),CAST(ROUND(SECONDS_BETWEEN('1900/01/01 00:00:00.000',CAST('2019-09-19 12:25:50.577' AS TIMESTAMP))/86400) AS INTEGER)
43725,43726
1 row selected (overall time 68,392 msec; server time 144 usec)
hdbsql SP4=>
Other related posts: