Convert and organize the built-in functions in Oracle in SQL

Source: Internet
Author: User
In the program, Oracle databases and SQL databases are often supported. Some Oracle built-in functions are mostly used, but the syntax in SQL is somewhat different. I have done some sorting and hope to help you .... 1. built-in functions in oracle: round functions in oracle (rounding) (SQL can also be used) returned by trunc in oracle

In the program, Oracle databases and SQL databases are often supported. Some Oracle built-in functions are mostly used, but the syntax in SQL is somewhat different. I have done some sorting and hope to help you .... 1. built-in functions in oracle: round functions in oracle (rounding) (SQL can also be used) returned by trunc in oracle

In the program, Oracle databases and SQL databases are often supported. Some Oracle built-in functions are mostly used in Hong Kong space, but the syntax in SQL is somewhat different. I have done some sorting and I hope to help you with the website space ....

1. built-in functions in oracle:
Round Function in oracle (rounding) (SQL can also be used)
Trunc in oracle Returns [00:00:00] of the current day.
In oracle, the floor and floor (n) values are the largest integers less than or equal to the value n (SQL can also be used)
The length function in oracle is len in SQL Server.
In oracle, The datalength function in sqlserver is datalength.
Change the lob_to_char function in oracle to convert (char, opertime, 120)
Change the to_char function in oracle to convert (char, opertime, 120)
Change the substr function in oracle to substring.
Example: select convert (char, getdate (), 120) -- yyyy-mm-dd hh: mm: ss (24 h)
Convert (char, SubmitTime, 120) = convert (char, getdate (), 120)
In oracle, change floor (sysdate-trunc (opertime) to datediff (dd, convert (varchar (100), InputDate, 111), convert (varchar (100), getdate (), 111 ))
Change dbms_lob.getlength in oracle to datalength (Change Clob type to NText type in sqlserver database)
Change ROWNUM in oracle to row_number () OVER (order by xx) and the SQL statement must contain top
For example, in SQL:
SELECT * FROM (
Select a. *, row_number () OVER (order by a. MATTERID) as rn from (
SELECT * FROM ATTACHMENT_T)
) T where rn <= 20 and rn> = 0

2. Oacle system table dual
Certificate to sqlserver directly remove dual

3. Errors and solutions:
SqlDateTime overflow. It must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM
Modify the constructor and server space in the model and assign the DateTime type to the attribute.
ElementContent_Model, OperLog_Model

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.