T-SQL date functions

--  ----------------------- Date and Time type ---------------------------------  --  -The T-SQL year, month, day function returns the year information for a specific date in an int Data Type format.  Select   '  Year Number  '   =   Year ( Getdate  ())  Select   '  Year Number  '  =   Month ( Getdate  ())  Select   '  Year Number  '   =   Day ( Getdate  ())  --  Get Date and Time Value part datepart  Datepart ( Datepart , Date data)  --  Datepart: the format of the data to be returned.Code  --  Date data: an expression, column expression, user-defined variable, or string text that can be parsed as time, date, smalldatetime, datetime datetime2 values  Select   Datepart ( Year , Getdate ()) As   '  Year 1  '  Select  Datepart (Yyyy, Getdate ()) As   '  Year 2  '  Select   Datepart (Yy, Getdate ()) As   '  Year 3  '  --  Date addition dateadd  -- The dateadd function adds a specific time interval value to a date and returns the datetime data type value.  --  1 syntax  Dateadd ( Datepart , Number  , Date data)  Select   Top   100 Indate order date, Dateadd ( Day , 14 , Indate) ' Date after addition  ' , *   From DBO. somaster With  (Nolock)  Order   By  Indate  /* The date after the order date is added 00:11:59. 000 00:11:59. 0002010-09-23 00:12:29. 000 00:12:29. 0002010-09-23 00:12:57. 000 00:12:57. 0002010-09-23 00:21:21. 000 00:21:21. 0002010-09-23 00:23:57. 000 00:23:57. 0002010-09-23 00:27:06. 000 00:27:06. 0002010-09-23 00:32:11. 000 00:32:11. 0002010-09-23 00:47:11. 000 00:47:11. 0002010-09-23 00:47:33. 000 00:47:33. 0002010-09-23 00:48:10. 000 00:48:10. 000  */  --  Date Subtraction  Select   Top   10 Indate order date,Datediff ( Day , Indate, '  2010-10-10 00:00:00. 000  ' ) '  Date after subtraction  '   From DBO. somaster With  (Nolock)  Order   By  Indate  /* Order date minus 00:11:59. 000 172010-09-23 00:12:29. 000 172010-09-23 00:12:57. 000 172010-09-23 00:21:21. 000 172010-09-23 00:23:57. 000 172010-09-23 00:27:06. 000 172010-09-23 00:32:11. 000 172010-09-23 00:47:11. 000 172010-09-23 00:47:33. 000 172010-09-23 00:48:10. 000 17  */    Select Sysdatetime () --  Obtain system time  Union   All  Select Sysdatetimeoffset () --  Get current date and time --  Determine whether it is a date data isdate (expression) -- if the expression is a valid date data: 1, otherwise: 0   --  -- Select isdate (orderid) from shippingorderextend with (nolock) Where orderid = 1019506  Declare   @ AA   Int  Select   Isdate ( @ AA  )  /*  **************************************** *******************************  */ --  Obtains the ASCII code of a character.  --  ASCII code is the standard character encoding. You can call the ASCII function to obtain the ASCII code of a character.  Set Nocount On   --  Get the temporary variable at the start of the ASCII code  Declare   @ Beginposition   Int  --  Defines string variables and stores the strings for obtaining ASCII codes.  Declare   @ Tempstring  Varchar ( 100  )  --  Initialize variable  Set   @ Beginposition   =   1  Set   @ Tempstring   =   '  Dancewithwave@163.com  '  -- Execute the ASCII function cyclically for each character in the string.  While   @ Beginposition   <   =   Datalength ( @ Tempstring  )  Begin       Select   ASCII ( Substring ( @ Tempstring , @ Beginposition , 1 )),  Char ( ASCII ( Substring ( @ Tempstring , @ Beginposition , 1  )))  Set   @ Beginposition   =   @ Beginposition   +   1      End Set Nocount Off  Go     

 

 

 

-- From <T-SQL Management and Development Instance essence>