How to use substring in a database CONVERT (varchar (), GETDATE (), 112)

SQL Server often to operate some time type of field conversion, I do not quite remember, so collected some of the following Sqlserverconvertdatetime related information published in their own small station, convenient for their later use when looking for, hope to everyone also helpful.

Change the [datetime] field value ' 2007-11-0716:41:35.033 ' to ' 2007-11-0700:00:00 ' for table tables in SQL Server to remove time and seconds. The [datetime] field is for the datetime type OH. UPDATE table set[datetime]= Convert (char (one), [datetime],120)

1. Get the current date using convert to convert to the datetime format we need. It seems that Oracle's plsql can not be used directly ... Only adapt to databases such as SQL Server???
Select CONVERT (varchar), GETDATE (), 112) similar to To_char (Xsdate, ' yyyymm ') in Oracle
20040912
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 102)
2004.09.12
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 101)
09/12/2004
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 103)
12/09/2004
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 104)
12.09.2004
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 105)
12-09-2004
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 106)
12 09 2004
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 107)
09 12, 2004
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 108)
11:06:08
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 109)
09 12 2004 1
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 110)
09-12-2004
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 113)
12 09 2004 1
------------------------------------------------------------
Select CONVERT (varchar), GETDATE (), 114)
11:06:08.177
------------------------------------------------------------

Declare @dateTime datetime--to define a dateTime variable
Set @dateTime =getdate (); --Get the current time of the system and assign a value to the @datetime field

--Short Date format: yyyy-m-d
Selectreplace (CONVERT (varchar), @dateTime, +), N '-0 ', '-')

--Long date format: yyyy mm month DD Day
Selectstuff (STUFF (CONVERT (char (8), @dateTime,), 5,0,n '), 8,0,n ' month ') +n ' Day '

--Long date format: YYYY year M D Day
SELECT datename (year, @dateTime) +n ' Years ' +cast (DATEPART (month, @dateTime) as varchar) +n ' month ' +datename (day, @dateTime) +n ' th '

--Full Date + time format: Yyyy-mm-dd hh:mi:ss:mmm
Selectconvert (char (one), @dateTime,) +convert (char (), @dateTime, 114)

------------------------------------------------------------------------------------------------
2, substring, the function is used to find a string of strings, the function is used very high frequency. Nor is it a usage in Oracle
Example string: "2011-11-17"
The function to find a string in Oracle is: SUBSTR
The syntax for the SUBSTR function is:
substr (String, start_position, [length])
String: Source string
Start_position: The starting position of the first character of a substring in the source string
Length: Sub-string Lengths
Test results:
1.
substr (' 2011-11-17 ', 0,7)
2011-11
2.
substr (' 2011-11-17 ', 1,7)
2011-11
3.
substr (' 2011-11-17 ', 1,10)
2011-11-17
4.
substr (' 2011-11-17 ', 1,11)
2011-11-17
5.
substr (' 2011-11-17 ', -1,7)
7
6.
substr (' 2011-11-17 ', -8,8)
11-11-17
7.
substr (' 2011-11-17 ', -10,7)
2011-11
8.
substr (' 2011-11-17 ', -11,7)
Null
9.
substr (' 2011-11-17 ',-11)
Null
10.
substr (' 2011-11-17 ',-1)
7
11.
substr (' 2011-11-17 ', 6)
11-17
12.
substr (' 2011-11-17 ', 11)
Null
13.
substr (' 2011-11-17 ', 1,null)
Null
14.
substr (' 2011-11-17 ', null,1)
Null
15.
substr (' 2011-11-17 ', null,null)
Null
16.
substr (' 2011-11-17 ', 1,0)
Null
17.
substr (' 2011-11-17 ', 1,-1)
Null
18.
substr (' 2011-11-17 ', 11,11)
Null
Oracle provides:
1) When start_position==0, the starting position of the substring is 1, which starts from the first character;
2) When start_position<0, the starting position of the string is pushed from the end of the substring.
3) The length parameter can be the default

3. Date Calculation processing
Oracle Time Function (sysdate) TO_CHAR is kingly: Oracle
1): Get the current date is the first week of this month

Sql> Select To_char (sysdate, ' YYYYMMDD W HH24:MI:SS ') from dual;
To_char (sysdate, ' YY
-------------------
20030327 4 18:16:09
Sql> Select To_char (sysdate, ' W ') from dual;
T
-
4
2: Get the current date is the day of the one week, note that Sunday is the first

Sql> Select Sysdate,to_char (sysdate, ' D ') from dual;
Sysdate T
--------- -
27-MAR-03 5
Similar:

Select To_char (sysdate, ' yyyy ') from dual; -year
Select To_char (sysdate, ' Q ' from dual;--season
Select To_char (sysdate, ' mm ') from dual; --month
Select To_char (sysdate, ' DD ') from dual; --Day
The day of the DDD year
The first few weeks of the WW year
W the first few weeks of the month
Day of the week in D week
HH hours (12)
Hh24 hours (24)
Mi points
SS sec
3: Take the current date is the day of the week in Chinese display:

Sql> Select To_char (sysdate, "Day") from dual;
To_char (sysdate, ' Day ')
----------------------
Thursday

4: If a table has an index on a field of date type, 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 the day 0:0 A.M. 0 seconds

Select Trunc (sysdate) from dual;
--Get the last second of the day
Select Trunc (sysdate) + 0.99999 from dual;
--Get the exact number of hours
Select Trunc (sysdate) + 1/24 from dual;
Select Trunc (sysdate) + 7/24 from dual;

7. Get the date of tomorrow 0:0 A.M. 0 seconds

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

8: Date of the month

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

9: Get the day of next month

Select Trunc (Add_months (sysdate,1), ' mm ') from dual;

10: Return 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: Determine whether a day is the year of the run year

Select Decode (To_char (Last_day (trunc (sysdate, ' y ') +31), ' DD '), ' 29 ', ' Leap Year ', ' Common year ') from dual;

15: Judge whether the year after two years

Select Decode (To_char (Last_day (Add_months (sysdate,24), ' y '), ' DD '), ' 29 ', ' Leap Year ', ' Common year ') from dual;

16): Get the date of the quarter
Select Ceil (To_number (To_char (sysdate, ' mm '))/3) from dual;
Select To_char (sysdate, ' Q ') from dual;

(iv) Other time processing methods:

DECLARE @dt datetime

SET @dt =getdate ()
DECLARE @number int
SET @number =3
--1. Specify a date 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. The first or last day of the quarter on which the specified date is located
--a. 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 (case judgment method)
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 ' ELSE ' ' END ')
--c. The last day of the quarter (direct extrapolation method)
SELECT DATEADD (Day,-1,
CONVERT (char (8),
DATEADD (Month,
1+datepart (Quarter, @dt) *3-month (@dt),
@dt),
120) + ' 1 ')
--3. The first or last day of the month in which the specified date is located
--a. 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. Last day of the month (easy-to-use error method)
SELECT DATEADD (Month,1,dateadd (Day,-day (@dt), @dt))
--4. Any day of the week on which the specified date is located
Selectdateadd (Day, @number-datepart (Weekday, @dt), @dt)
--5. Any day of the week on which the specified date is located
--a. Sunday as the 1th day of the week
Selectdateadd (Day, @number-(DATEPART (Weekday, @dt) [email protected] @DATEFIRST-1)%7, @dt)
--b. Monday as the 1th day of the week
Selectdateadd (Day, @number-(DATEPART (Weekday, @dt) [email protected] @DATEFIRST-2)%7-1, @dt)

How to use substring in a database CONVERT (varchar (), GETDATE (), 112)