Date processing in SQL

Date processing in SQL

Select CONVERT (DateTime, ' 10/dec/2009 12:22:31.120 ') from OPENROWSET (' SQLOLEDB ', ' 10.10.80.83 '); ' Sa '; ' Password#1 ', creditzj.dbo.X1)
Set language ' Simplified Chinese '

--datepart abbreviation
--year yy, yyyy
--Quarterly QQ, Q
--month mm, m
--Japanese dy in the year, Y
--day DD, D
--Zhou Wk, ww
--Week DW, W
--hour HH
-Min mi, n
--second SS, S
--Millisecond MS
--Subtle MCs
-nanosecond NS

Select GETDATE ()--Gets the date of the current system

Common usage of--dateadd functions
--dateadd () The date after the specified value is added to the specified date part

Select DATEADD (Yy,4,getdate ())
Select DATEADD (Dd,4,getdate ())
Select DATEADD (Mm,4,getdate ())
Select DATEADD (Hh,4,getdate ())

Common usage of--datediff functions
--datediff () The difference between a specified date part between two dates, returning an integer;
--The second argument can be an integer, if the integer is a positive number, and the specified value is added in the third argument
--If the integer is negative, subtract the specified number in the third argument

Select DateDiff (dd, ' 01/01/2011 ', ' 05/01/2011 ')
Select DateDiff (Dd,3,getdate ())
Select Convert (Datetime,datediff (Dd,3,getdate ())

--A string form of the date part specified in the date
Select Datename (Yy,getdate ())
Select Datename (Mm,getdate ())
Select Datename (Dd,getdate ())
Select Datename (Dw,getdate ())

--integer form of the date part specified in the date
Select DatePart (Yy,getdate ())
Select DatePart (Mm,getdate ())
Select DatePart (Dd,getdate ())
Select DatePart (Dw,getdate ())

Date processing in. Net

Format Pattern Description
D One day of the month. The date of one number does not have a leading zero.
One day of DD month. The date of a single number has a leading zero.
The abbreviated name of a day in the DDD week, defined in Abbreviateddaynames.
dddd the full name of the day of the week, defined in DayNames.
M-month numbers. A single number of months does not have a leading zero.
MM month number. A one-digit month has a leading zero.
The abbreviated name of the MMM month, defined in AbbreviatedMonthNames.
The full name of the MMMM month, defined in MonthNames.
Y does not contain the year of the era. If the year that does not contain the era is less than 10, the year is displayed with no leading zeros.
YY does not contain years of the era. If the year that does not contain the era is less than 10, the year with leading zeros is displayed.
YYYY includes the four-digit year of the era.
GG period or ERA. If the date to be formatted does not have an associated period or era string, the pattern is ignored.
H 12 Hour system. The number of hours in a single digit does not have a leading zero.
HH 12-hour hour. The number of hours in a single digit has a leading zero.
H 24 hour system. The number of hours in a single digit does not have a leading zero.
HH 24-hour hour. The number of hours in a single digit has a leading zero.
M minutes. The number of minutes in one digit does not have a leading zero.
MM minutes. The number of minutes in a single digit has a leading zero.
s seconds. A number of seconds does not have a leading zero.
SS seconds. The number of seconds in a single digit has a leading zero.
The fractional precision of f seconds is one digit. The remaining digits are truncated.
The fractional precision of FF seconds is two digits. The remaining digits are truncated.
The decimal precision for fff seconds is three digits. The remaining digits are truncated.
The decimal precision for ffff seconds is four digits. The remaining digits are truncated.
The decimal precision for fffff seconds is five digits. The remaining digits are truncated.
The decimal precision for ffffff seconds is six digits. The remaining digits are truncated.
The decimal precision for fffffff seconds is seven digits. The remaining digits are truncated.
t the am/pm defined in AMDesignator or PMDesignator indicates the first character of the item, if one exists.
The AM/PM indication (if any) that TT is defined in AMDesignator or PMDesignator.
Z Time zone offset ("+" or "-" followed only by hours). The number of hours in a single digit does not have a leading zero. For example, the Pacific Standard Time is "-8".
The ZZ Time zone offset ("+" or "-" follows only the hour). The number of hours in a single digit has a leading zero. For example, the Pacific Standard Time is "-08".
ZZZ Full time zone offset ("+" or "-" followed by hours and minutes). The number of hours and minutes for a single digit has a leading zero. For example, the Pacific Standard Time is " -08:00".
: The default time separator defined in TimeSeparator.
/the default date separator defined in DateSeparator.
% c where C is the format pattern (if used alone). If the format pattern is merged with a literal character or another format pattern, you can omit the "%" character.
c where c is any character. Display characters according to the literal meaning. To display the backslash character, use.
　
DateTime dt = DateTime.Now;
Label1.Text = dt. ToString ();//2005-11-5 13:21:25
Label2.Text = dt. Tofiletime (). ToString ();//127756416859912816
Label3.text = dt. TOFILETIMEUTC (). ToString ();//127756704859912816
Label4.text = dt. ToLocalTime (). ToString ();//2005-11-5 21:21:25
Label5.text = dt. Tolongdatestring (). ToString ();//November 5, 2005
Label6.text = dt. Tolongtimestring (). ToString ();//13:21:25
Label7.text = dt. ToOADate (). ToString ();//38661.5565508218
Label8.text = dt. ToShortDateString (). ToString ();//2005-11-5
Label9.text = dt. Toshorttimestring (). ToString ();//13:21
Label10.text = dt. ToUniversalTime (). ToString ();//2005-11-5 5:21:25
Label1.Text = dt. Year.tostring ();//2005
Label2.Text = dt. Date.tostring ()//2005-11-5 0:00:00
Label3.text = dt. Dayofweek.tostring ();//saturday
Label4.text = dt. Dayofyear.tostring ();//309
Label5.text = dt. Hour.tostring ();//13
Label6.text = Dt.Millisecond.ToString ();//441
Label7.text = dt. Minute.tostring ();//30
Label8.text = dt. Month.tostring ();//11
Label9.text = dt. Second.tostring ();//28
Label10.text = dt. Ticks.tostring ();//632667942284412864
Label11.text = dt. Timeofday.tostring ();//13:30:28.4412864
Label1.Text = dt. ToString ();//2005-11-5 13:47:04
Label2.Text = dt. Addyears (1). ToString ();//2006-11-5 13:47:04
Label3.text = dt. AddDays (1.1). ToString ();//2005-11-6 16:11:04
Label4.text = dt. AddHours (1.1). ToString ();//2005-11-5 14:53:04
Label5.text = dt. Addmilliseconds (1.1). ToString ();//2005-11-5 13:47:04
Label6.text = dt. Addmonths (1). ToString ();//2005-12-5 13:47:04
Label7.text = dt. AddSeconds (1.1). ToString ();//2005-11-5 13:47:05
Label8.text = dt. AddMinutes (1.1). ToString ();//2005-11-5 13:48:10
Label9.text = dt. Addticks (1000). ToString ();//2005-11-5 13:47:04
Label10.text = Dt.compareto (dt). ToString ();//0
Label11.text = dt. ADD (?). ToString ()//question mark is a time period
Label1.Text = dt. Equals ("2005-11-6 16:11:04"). ToString ();//false
Label2.Text = dt. Equals (DT). ToString ();//true
Label3.text = dt. GetHashCode (). ToString ();//1474088234
Label4.text = dt. GetType (). ToString ();//system.datetime
Label5.text = dt. GetTypeCode (). ToString ();//datetime
Label1.Text = dt. Getdatetimeformats (' s ') [0]. ToString ();//2005-11-05t14:06:25
Label2.Text = dt. Getdatetimeformats (' t ') [0]. ToString ();//14:06
Label3.text = dt. Getdatetimeformats (' y ') [0]. ToString ();//November 2005
Label4.text = dt. Getdatetimeformats (' D ') [0]. ToString ();//November 5, 2005
Label5.text = dt. Getdatetimeformats (' D ') [1]. ToString ();//2005 11 05
Label6.text = dt. Getdatetimeformats (' D ') [2]. ToString ();//Saturday 2005 11 05
Label7.text = dt. Getdatetimeformats (' D ') [3]. ToString ()//Saturday November 5, 2005
Label8.text = dt. Getdatetimeformats (' M ') [0]. ToString ();//November 5
Label9.text = dt. Getdatetimeformats (' f ') [0]. ToString ();//November 5, 2005 14:06
Label10.text = dt. Getdatetimeformats (' g ') [0]. ToString ();//2005-11-5 14:06
Label11.text = dt. Getdatetimeformats (' R ') [0]. ToString ();//sat, Nov 14:06:25 GMT
Label1.Text = string. Format ("{0:d}", DT);//2005-11-5
Label2.Text = string. Format ("{0:d}", dt);//November 5, 2005
Label3.text = string. Format ("{0:f}", dt);//November 5, 2005 14:23
Label4.text = string. Format ("{0:f}", dt);//November 5, 2005 14:23:23
Label5.text = string. Format ("{0:g}", DT);//2005-11-5 14:23
Label6.text = string. Format ("{0:g}", DT);//2005-11-5 14:23:23
Label7.text = string. Format ("{0:m}", dt);//November 5
Label8.text = string. Format ("{0:r}", DT);//sat, Nov-14:23:23 GMT
Label9.text = string. Format ("{0:s}", DT);//2005-11-05t14:23:23
Label10.text string. Format ("{0:t}", DT);//14:23
Label11.text = string. Format ("{0:t}", DT);//14:23:23
Label12.text = string. Format ("{0:u}", DT);//2005-11-05 14:23:23z
Label13.text = string. Format ("{0:u}", dt);//November 5, 2005 6:23:23
Label14.text = string. Format ("{0:y}", dt);//November 2005
Label15.text = string. Format ("{0}", dt);//2005-11-5 14:23:23
Label16.text = string. Format ("{0:yyyymmddhhmmssffff}", DT);