How to Use substring in oracle Database CONVERT (varchar (12), getdate (), 112), substringgetdate

How to Use substring in oracle Database CONVERT (varchar (12), getdate (), 112), substringgetdate

SqlserverI often need to perform some time-type field conversions, but I don't quite remember it. So I collected the following SqlserverConvertDateTime-related materials and posted them on my own site, it is convenient for you to search for it later. I hope it will be helpful to you.

Change the value of the [datetime] field in the table in SQL Server to '2017-11-2007: 41: 100' to '2017-11-0716: 00: 00' to remove the time, minute, and second. [datetime] the field must be of the datetime type. UPDATE table SET [datetime] = Convert (char (11), [datetime], 120)

1. Get the current date and convert it to the datetime format we need using convert.It seems that oracle PLSQl cannot be used directly...
Select CONVERT (varchar (12), getdate (), 112)
20040912
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 102)
2004.09.12
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 101)
09/12/2004
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 103)
12/09/2004
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 104)
12.09.2004
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 105)
12-09-2004
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 106)
12 09 2004
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 107)
09 12,200 4
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 108)
11:06:08
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 109)
09 12, 2004 1
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 110)
09-12-2004
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 113)
12 09 2004 1
------------------------------------------------------------
Select CONVERT (varchar (12), getdate (), 114)
11:06:08. 177
------------------------------------------------------------

Declare @ dateTime DateTime -- defines a datetime variable
Set @ dateTime = getdate (); -- Obtain the current system time and assign it to the @ dateTime Field

-- Short Date Format: yyyy-m-d
SELECTREPLACE (CONVERT (varchar (10), @ dateTime, 120), N'-0 ','-')

-- Long Date Format: yyyy-mm-dd
SELECTSTUFF (STUFF (CONVERT (char (8), @ dateTime, 112), 5, 0, N 'Year'), 8, 0, N 'month') + N'

-- Long Date Format: yyyy-mm-dd
Select datename (Year, @ dateTime) + N 'Year' + CAST (DATEPART (Month, @ dateTime) AS varchar) + N 'month' + DATENAME (Day, @ dateTime) + n'day'

-- Complete date + time format: yyyy-mm-dd hh: mi: ss: mmm
SELECTCONVERT (char (11), @ dateTime, 120) + CONVERT (char (12), @ dateTime, 114)

Bytes ------------------------------------------------------------------------------------------------
2. substring: This function is used to evaluate a string. This function is frequently used.
Example string: "2011-11-17"
In Oracle, the string function is substr.
The syntax for the substr function is:
Substr (string, start_position, [length])
String: Source string
Start_position: Start position of the first character of the substring in the source string
Length: the length of the substring.
Test results:
1.
Substr ('2017-11-17 ', 2011)
2011-11
2.
Substr ('2017-11-17 ', 2011)
2011-11
3.
Substr ('2017-11-17 ', 2011)
2011-11-17
4.
Substr ('2017-11-17 ', 2011)
2011-11-17
5.
Substr ('2017-11-17 ',-2011)
7
6.
Substr ('2017-11-17 ',-8)
11-11-17
7.
Substr ('2017-11-17 ',-2011)
2011-11
8.
Substr ('2017-11-17 ',-11,7)
Null
9.
Substr ('2017-11-17 ',-11)
Null
10.
Substr ('2017-11-17 ',-1)
7
11.
Substr ('2017-11-17 ', 6)
11-17
12.
Substr ('2017-11-17 ', 11)
Null
13.
Substr ('2017-11-17 ', 1, null)
Null
14.
Substr ('2017-11-17 ', null, 1)
Null
15.
Substr ('2017-11-17 ', null, null)
Null
16.
Substr ('2017-11-17 ', 2011)
Null
17.
Substr ('2017-11-17 ', 1,-1)
Null
18.
Substr ('2017-11-17 ', 2011)
Null
Oracle stipulates:
1) When start_position = 0, the starting position of the substring is 1, that is, starting from the first character;
2) When start_position <0, the start position of the string starts from the end of the string.
3) The length parameter can be set by default.

3. Date Calculation

Oracle time functions (sysdate)

1): Obtain the week number of the current month.

SQL> select to_char (sysdate, 'yyyymmdd W HH24: MI: ss') from dual; TO_CHAR (SYSDATE, 'yy ------------------- 4 18:16:09 20030327 SQL> select to_char (sysdate, 'w') from dual; T - 4

2: The current date is the day of the week. Note that Sunday is the first day of the week.

SQL> select sysdate, to_char (sysdate, 'D') from dual; SYSDATE T ---------- 27-MAR-03 5

For example:

Select to_char (sysdate, 'yyyy') from dual; -- year Select to_char (sysdate, 'q' from dual; -- quarter Select to_char (sysdate, 'mm') from dual; -- month Select to_char (sysdate, 'dd') from dual; -- day The day of the year in ddd The week in WW W the week of the month D. day of the week Hh hour (12) Hh24 (24) Mi score Ss seconds

3: display the current date in Chinese on the day of the week:

SQL> select to_char (sysdate, 'day') from dual; TO_CHAR (SYSDATE, 'day ') ---------------------- Thursday

4: If a table is indexed on a date field, how to use

Alter session set NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: ss'

5: Get the current date

Select sysdate from dual;

6: Get the date of 00:00:00 that day

Select trunc (sysdate) from dual; -- Get the last second of the day Select trunc (sysdate) + 0.99999 from dual; -- Obtain the hour value. Select trunc (sysdate) + 1/24 from dual; Select trunc (sysdate) + 7/24 from dual;

7. Get the date of 00:00:00 tomorrow morning.

Select trunc (sysdate + 1) from dual; Select trunc (sysdate) + 1 from dual;

8: date of January 1, 1st day of this month

Select trunc (sysdate, 'mm') from dual;

9: Get the date of January 1, 1st day of next month.

Select trunc (add_months (sysdate, 1), 'mm') from dual;

10: returns the last day of the current month?

Select last_day (sysdate) from dual; Select last_day (trunc (sysdate) from dual; Select trunc (last_day (sysdate) from dual; Select trunc (add_months (sysdate, 1), 'mm')-1 from dual;

11: Get every day of the year

Select trunc (sysdate, 'yyyy') + rn-1 date0 From (Select rownum rn from all_objects Where rownum <366 );

12: Today is the nth day of the year

SELECT TO_CHAR (SYSDATE, 'ddd ') from dual;

13: how to add 2 years to an existing date

Select add_months (sysdate, 24) from dual;

14: determines whether the year of a certain day is a runyear.

Select decode (to_char (last_day (trunc (sysdate, 'y') + 31), 'dd'), '29', 'leap year', 'Year') from dual;

15: judge whether it is a runyear after two years

Select decode (to_char (last_day (trunc (add_months (sysdate, 24), 'y') + 31), 'dd'), '29', 'leap year ', 'Year') from dual;

16): Obtain the quarter of the date
Select ceil (to_number (to_char (sysdate, 'mm')/3) from dual;
Select to_char (sysdate, 'q') from dual;

4) Other Time Processing Methods:

DECLARE @ dt datetime

SET @ dt = GETDATE ()
DECLARE @ number int
SET @ number = 3
-- 1. Specify the first or last day of the year
-- A. The first day of the year
Select convert (char (5), @ dt, 120) + '1-1'
-- B. The last day of the year
Select convert (char (5), @ dt, 120) + '12-31'
-- 2. Specify the first or last day of the quarter where the date is located.
-- A. The first day of the quarter
Select convert (datetime,
CONVERT (char (8 ),
DATEADD (Month,
DATEPART (Quarter, @ dt) * 3-Month (@ dt)-2,
@ Dt ),
120) + '1 ')
-- B. The last day of the quarter)
Select convert (datetime,
CONVERT (char (8 ),
DATEADD (Month,
DATEPART (Quarter, @ dt) * 3-Month (@ dt ),
@ Dt ),
120)
+ Case when datepart (Quarter, @ dt) in (1, 4)
THEN '31 'else' 30' END)
-- C. The last day of the quarter (Direct Algorithm)
Select dateadd (Day,-1,
CONVERT (char (8 ),
DATEADD (Month,
1 + DATEPART (Quarter, @ dt) * 3-Month (@ dt ),
@ Dt ),
120) + '1 ')
-- 3. Specify the first or last day of the month in which the date is located.
-- A. The first day of the month
SELECTCONVERT (datetime, CONVERT (char (8), @ dt, 120) + '1 ')
-- B. The last day of the month
SELECTDATEADD (Day,-1, CONVERT (char (8), DATEADD (Month, 1, @ dt), 120) + '1 ')
-- C. The last day of the month (the easy-to-use error method)
Select dateadd (Month, 1, DATEADD (Day,-DAY (@ dt), @ dt ))
-- 4. Specify any day of the week of the date
SELECTDATEADD (Day, @ number-DATEPART (Weekday, @ dt), @ dt)
-- 5. Specify any day of the week of the date
-- A. Sunday is the 1st day of A week.
SELECTDATEADD (Day, @ number-(DATEPART (Weekday, @ dt) + @ DATEFIRST-1) % 7, @ dt)
-- B. Monday is the 1st day of a week.
SELECTDATEADD (Day, @ number-(DATEPART (Weekday, @ dt) + @ DATEFIRST-2) % 7-1, @ dt)

Convert (varchar (8), getdate (), 112) uses this sentence to get the current date. What does the following parameter 112 mean?

Conversion functions
When Microsoft®SQL Server™2000 when expressions of different data types are not automatically converted, CAST and CONVERT functions can be used for conversion. These conversion functions can also be used to obtain various special data formats. Both conversion functions can be used to select the list, WHERE clause, and any place WHERE expressions are allowed.

If you want your Transact-SQL program code to comply with SQL-92 standards, use CAST instead of CONVERT. If you want to use the Style Function in CONVERT, use CONVERT instead of CAST.

When using CAST or CONVERT, two pieces of information are required:

Expression to be converted (for example, the sales report requires that the sales data be converted from currency data to currency data ).

The data type to be converted to, for example, any other data type provided by varchar or SQL Server.
Unless the converted value is stored, the conversion is only valid during the CAST or CONVERT function's validity period.

In the following example, CAST is used in the first SELECT statement and CONVERT is used in the second SELECT statement to CONVERT the title column to the char (50) column to make the result readable:

USE pubs
Select cast (title AS char (50), ytd_sales
FROM titles
WHERE type = 'trad _ cook'

Or

USE pubs
Select convert (char (50), title), ytd_sales
FROM titles
WHERE type = 'trad _ cook'

The following is the result set: (for any query)

Ytd_sales
----------------------------------------------------
Onions, Leeks, and Garlic: Cooking Secrets of the 375
Fifty Years in Buckingham Palace Kitchens 15096
Sushi, Anyone? 4095

(3 row (s) affected)

In the following example, the ytd_sales column of the int type is converted to the char (20) column, so that the LIKE predicate can be used for this column:

USE pubs
SELECT title, ytd_sales
FROM titles
Where cast (ytd_sales AS char (20) LIKE '20140901'
AND type = 'trad _ cook'

The following is the result set:

Title ytd_sales

--------------------------------------------------

Fifty Years in Buckingham Palace Kitchens 15096

(1 row (s) affected)

SQL Server automatically converts certain data types. For example, if you compare char and datetim ...... the remaining full text>

In CONVERT (VARCHAR, getdate (), 23), 23 indicates the specified return type. 23 indicates the specified return type?

Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2006 AM
Select CONVERT (varchar (100), GETDATE (), 1): 05/16/06
Select CONVERT (varchar (100), GETDATE (), 2): 06.05.16
Select CONVERT (varchar (100), GETDATE (), 3): 16/05/06
Select CONVERT (varchar (100), GETDATE (), 4): 16.05.06
Select CONVERT (varchar (100), GETDATE (), 5): 16-05-06
Select CONVERT (varchar (100), GETDATE (), 6): 16 05 06
Select CONVERT (varchar (100), GETDATE (), 7): 05 16, 06
Select CONVERT (varchar (100), GETDATE (), 8): 10:57:46
Select CONVERT (varchar (100), GETDATE (), 9): 05 16 2006 10: 57: 46: 827AM
Select CONVERT (varchar (100), GETDATE (), 10): 05-16-06
Select CONVERT (varchar (100), GETDATE (), 11): 06/05/16
Select CONVERT (varchar (100), GETDATE (), 12): 060516
Select CONVERT (varchar (100), GETDATE (), 13): 16 05 2006 10: 57: 46: 937
Select CONVERT (varchar (100), GETDATE (), 14): 10: 57: 46: 967
Select CONVERT (varchar (100), GETDATE (), 20): 10:57:47
Select CONVERT (varchar (100), GETDATE (), 21): 10:57:47. 157
Select CONVERT (varchar (100), GETDATE (), 22): 05/16/06 10:57:47 AM
Select CONVERT (varchar (100), GETDATE (), 23 ):
Select CONVERT (varchar (100), GETDATE (), 24): 10:57:47
Select CONVERT (varchar (100), GETDATE (), 25): 10:57:47. 250
Select CONVERT (varchar (100), GETDATE (), 100): 05 16 2006 AM
Select CONVERT (varchar (100), GETDATE (), 101): 05/16/2006
Select CONVERT (varchar (100), GETDATE (), 102): 2006.05.16
Select CONVERT (varchar (100), GETD ...... remaining full text>