SQL Function-zhuan

Oracle:

1. That Day is the day of the year

Select to_char (sysdate, 'ddd ') from dual

2. Move forward for a period of time

A. Seven days before the renewal

Select sysdate, sysdate-interval '7' day from dual

B. Move forward for 7 hours

Select sysdate, sysdate-interval '7' hour from dual

C. Go to the forward shard for 7 minutes.

Select sysdate, sysdate-interval '7' minute from dual

D. Seven months before the renewal

Select sysdate, sysdate-interval '7' month from dual

E. Go to the frontend for seven years.
 
Select sysdate, sysdate-interval '7' year from dual

F. Time multiplied by a number (16 hours forward)

Select sysdate, sysdate-8 * interval '2' hour from dual

3. Date-to-character operations

Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual

Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh: mi: ss') from dual

Select sysdate, to_char (sysdate, 'yyyy-ddd hh: mi: ss') from dual

Select sysdate, to_char (sysdate, 'yyyy-mm iw-d hh: mi: ss') from dual

4. The character-to-date operation is the inverse operation above.

5. display Two Hundred Twenty-Two
 
Select to_char (to_date (222, 'J'), 'jsp ') from dual

6. The day is the week of the week.

Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day') from dual

7. Date Format conflict

A. The input format depends on the type of the ORACLE character set you installed, for example, US7ASCII. The date format is '01-Jan-01'

Alter system set NLS_DATE_LANGUAGE = American

Alter session set NLS_DATE_LANGUAGE = American

B. Or write it in to_date.

Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ DATE_LANGUAGE = American') from dual;

C. Note that I only use NLS_DATE_LANGUAGE. Of course, there are many more. You can view

Select * from nls_session_parameters

Select * from V $ NLS_PARAMETERS

8. The last day of a month is the renewal period.

Select last_day (to_date ('to _ date ('2014/1/01', 'yyyy/mm/dd') from dual

9. Calculate the number of days in a year

Select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual // use the first day of the next year to go to the first day of the year

10. Processing Method in the latest year

Select to_char (last_day (to_date ('02 '| to_char (sysdate, 'yyyy'), 'mmyy'), 'dd ') from dual // if the result is 28, it is not the year of renewal.

11. next_day (sysdate, n)
 
(N =, 7 points apply to Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)
 

SQL Server

1. select convert (varchar (100), GETDATE (), 120): 10:57:49

2. select convert (varchar (100), GETDATE (), 112): 20060516

3. select convert (varchar (100), GETDATE (), 111): 2006/05/16

4. select convert (varchar (100), GETDATE (), 110): 05-16-2006

5. select convert (varchar (100), GETDATE (), 108): 10: 57: 49

6. select convert (varchar (100), GETDATE (), 101): 05/16/2006

7. select convert (varchar (100), GETDATE (), 102): 2006.05.16

8. select convert (varchar (100), GETDATE (), 103): 16/05/2006

. String letter?
? Degree and Analysis

Datalength (Char_expr) returns the string containing characters ?, But it does not contain spaces

Not many substring (expression, start, length? Get the substring

Right (char_expr, int_expr) returns the right of the string? Int_expr? Character

Character operations?

Upper (char_expr )?? Big?

Lower (char_expr )?? Small?

Space (int_expr) generates int_expr? Space

Replicate (char_expr, int_expr) copies the string int_expr times

Reverse (char_expr)? String

Stuff (char_expr1, start, length, char_expr2 )? In character string char_expr1?

Start? Start length? Character is replaced by char_expr2

Ltrim (char_expr) rtrim (char_expr) removes Spaces

Ascii (char) char (ascii )? Letter ???, Ascii ?, Based on ascii? Take characters

String search

Charindex (char_expr, expression) returns the starting position of char_expr

Patindex ("% pattern %", expression) returns the starting position of the specified mode, no ?? 0

2 .?? Letter?

Abs (numeric_expr ?? Value

Ceiling (numeric_expr) is the smallest integer greater than or equal to the specified value?

Exp (float_expr) refers?

Floor (numeric_expr) is less than or equal to the value of the specified maximum integer?

Pi () 1415926 .........

Power (numeric_expr, power) returns power to power

Rand ([int_expr])? Machine ?? Generator

Round (numeric_expr, int_expr) An int_expr? Precision rounding

Sign (int_expr) According to positive ?, 0 ,??,, +,-1

Sqrt (float_expr) Square Root

3. Date letter?

Getdate () returns the date

What is the return name of datename (datepart, date_expr? For example, June

Datepart (datepart, date_expr) is a part of the date.

Datediff (datepart, date_expr1.dateexpr2) date difference

Dateadd (datepart, number, date_expr)

The above letter? In datepart

? Method value and meaning?

Yy 1753-March

Qq: 1-4

Mm 1-March December

Dy 1-366

Dd 1-31

Wk 1-54 weeks

Dw 1-7 weeks

Hh 0-23 small?

Mi 0-59?

Ss 0-59 seconds

MS 0-999 Ms

Date ??

Convert ()

 

4. system? Letter?

Suser_name? Login? Name

User_name? In? Data? Name in

User? In? Data? Name in

Show_role ()?? Used before? What works ??

Db_name ()? Data? Name

Object_name (obj_id )? Data ?? Elephant name

Col_name (obj_id, col_id) column name

Col_length (objname, colname) column? Degrees

Is valid_name (char_expr) valid ?? Character

Cross-Region Query

SELECT * from OpenRowSet ('msdasql ', 'driver = SQL Server; Server = servername; Uid = username; PWD = password', databasename. dbo. tablename)

Select * into beol. dbo. Calendar_day from calendar_day // Copy Table Structure and Data

Select top 0 * into beol. dbo. Calendar_day from calendar_day // Just Copy Table Structure