Oracle and SQL Server use functions to generate date plus serial number

Source: Internet
Author: User
Tags getdate

Recently in Oracle-related projects, just contact with Oracle, and SQL Server syntax is still different

Sql server:

Example: fx+ Current Month day +00001

As shown in the strength of the serial number

Principle:

First ' FX ' is fixed, getting the current month and date method in SQL Server is:

1. Years: Year (GETDATE ())

2. Monthly: Month (GETDATE ())

3. Days: Day (GETDATE ())

The second most important is the serial number automatically add 1, the main idea is to get to the current table in the largest number of Fxnum (the last 6 bits of the largest number), and use the right function to obtain the last 6 bits and add 1.

The SQL function code is as follows:

Create function [dbo].[F_getfxnum]()returns varchar( the) asbeginDeclare @FxNum varchar( the)Declare @time varchar(8)Set @time=CONVERT(varchar, Year(GETDATE()))+right('xx'+CONVERT(varchar,Month(getdate())),2)+CONVERT(varchar, Day(GETDATE()))--take the current month daySelect @FxNum='FX'+@time+right(100000+ISNULL( Right(MAX(Fxnum),5),0)+1,5)--Gets the current table maximum value and adds 1 fromPx_fxrecordreturn @FxNumEndALTER TABLEPx_fxrecordADD DEFAULT([dbo].[F_getfxnum]()) forFxnum--bind the function to Fxnum as the default value for the field

Parameter description:

Right (Str,len) -The field returns the rightmost Len character string str
Max () -the largest value in the field
IsNull () -Determines whether the content is empty

convert () -format conversion

Oracle:

Oracle is simpler than the SQL syntax to refine a little, in fact, the same way, the encoding mode with ' FX ' after the beginning of the 8-month date plus 8-digit serial number, in the Table Query day maximum plus 1, if not, then from the system time the smallest start (fx+ current Month day +000001), has been added 1.

The right function in SQL is replaced with SUBSTR in Oracle

The Oracle function code is as follows:

Create or Replace functionF_getfxnumreturnnvarchar2 asfxnum nvarchar2 ( -);beginSELECT 'FX'||(NVL (MAX(SUBSTR (Fxnum,3, -), To_char (Sysdate,'YYYYMMDD')||'000000')+1) intoFxnum fromPx_fxrecordWHERESUBSTR (Fxnum,3,8)=To_char (Sysdate,'YYYYMMDD') ;returnFxnum;End;

Parameter description:

1.substr (str, intercept start position, len) //Returns the intercepted Word, right (Str,len) returns the Len string starting from the rightmost

2.to_char (sysdate, ' YYYYMMDD ') //Return to current month day

3.NVL () // NVL (STR1,STR2) If the first parameter of Oracle is empty then the value of the second parameter is displayed, and if the value of the first parameter is not NULL, the first parameter is displayed.

Summarize

1.oracle and SQL Server are similar in principle, based on SQL development, except that the syntax is implemented differently.

2. Of course, the implementation of the serial number is more than the function of this way, you can also use stored procedures to achieve, interested friends can study.

Oracle and SQL Server use functions to generate date plus serial number

Related Article

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.