How to Use substring in database CONVERT (varchar (12), getdate (), 112

In SQL Server, I 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 site, it is convenient for you to search for it later. I hope it will be helpful to you. set the value of the [datetime] field of the table in sqlserver to 2007-11-0716: 41:

In SQL Server, I 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 site, it is convenient for you to search for it later. I hope it will be helpful to you. set the value of the [datetime] field of the table in sqlserver to 2007-11-0716: 41:

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... Only applicable to databases like SQL server ???
Select CONVERT (varchar (12), getdate (), 112) is similar to to_char (xsdate, 'yyymmm ') in oracle ')
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. It is not an oracle usage.
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 function (sysdate) to_char is King: oracle

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)