Summary of commonly used functions and time processing in SqlServer
YEAR() The function returns an integer value that represents the year of the specified date, typically used as Year, such as:YEAR('2023-03-1416:50:08.543')--2023REPLACE() The string replacement function is generally used as: Replace (the searched string, the string to be replaced, and the replacement value), such as:REPLACE ('Test Added ',' of ',' Test Data ') -- Test Added Test DataSTUFF() The function is used to delete a specified length of character and insert another set of characters at the specified starting point
YEAR() The function returns an integer value that represents the year of the specified date, typically used as Year, such as:
YEAR('2023-03-1416:50:08.543')--2023REPLACE() The string replacement function is generally used as: Replace (the searched string, the string to be replaced, and the replacement value), such as:
REPLACE ('Test Added ',' of ',' Test Data ') -- Test Added Test DataSTUFF() The function is used to delete a specified length of character and insert another set of characters at the specified starting point. It is generally used as: STUFF (string, starting from 1 to calculate the starting position of the character, the number of characters to be deleted, and the string to be reinserted), such as:
STUFF ('Test Added ', 3,2,' Modify ') -- Test ModifiedDATEADD() The function adds a numerical value to the specified date part of the input date and returns the modified value, typically used as: DATEADD (which part of time needs to be operated on, time, interval– negative in the past and positive in the future), such as:
DATEADD(Year,-7,GETDATE())`--2016-03-1416:48:01.317
DATEDIFF() The function returns the difference between two dates. The general usage is: DATEDIFF (requiring time difference, start time, end time), such as:
DATEDIFF (MS, '2023 03 1416:45:08.543', '2023 03 1416:50:08.543') --300000 (milliseconds) DATEDIFF (SS, '2023 03 1416:45:08.543', '2023 03 1416:50:08.543') --300 (seconds) DATEDIFF (MI, '2023 03 1416:45:08.543', '2023 03 1416:50:08.543') --5 (minutes) DATEDIFF (HOUR, '2023 03 1412:50:08.543', '2023 03-1416:50:08.543' -4 (hours) DATEDIFF (DAY, '2023 03 0116:50:08.543', '2023 03 1416:50:08.543') -13 (days) DATEDIFF (WEEK, '2023 03 0116:50:08.543', '2023 03 1416:50:08.543') -2 (Week) DATEDIFF (MONTH, '2023 02 0116:50:08.543', '2023 03 1416:50:08.543') -1 (Month) DATEDIFF (QUARTER, '2023 01 0116:50:08.543', '2023 04 1416:50:08.543') -1 (Quarter) DATEDIFF (YEAR, '2022 02 0116:50:08.543', '2023-03-1416:50:08.543' -1 (year)
Some commonly used SQL time related queries
--Retrieve the month day portion of the current time and process it in the format of [Month/Day] SelectSTUFF (REPLACE (CONVERT (VARCHAR (10), GETDATE(), 111), YEAR (GETDATE()), ''), 1,1, '') --03/14-- Retrieve the first 7 days of the current time. If you need to retrieve the first and last few days, modify the value of [-7] SelectDATEADD (DAY, DATEDIFF (DAY, 0, GETDATE()), -7) --2023-03-0700:00:00.000-- Retrieve the last day of the month Date SelectCONVERT (VARCHAR (10), DATEADD (MONTH, DATEDIFF (MONTH, -1, GETDATE()), -1), 23) --2023-03-31-- Retrieve the last day of the previous month SelectCONVERT (VARCHAR (10), DATEADD (MONTH, DATEDIFF (MONTH, 0, GETDATE()), -1), 23) --2023-02-28-- Select the first day of the year DATEADD (YEAR, DATEDIFF (YEAR, 0, GETDATE()), 0) --2023-01-0100:00.000- On the last day of this year, SelectDATEADD (YEAR, DATEDIFF (YEAR, 0, DATEADD (YEAR, 1, GETDATE())), -1) -2023-12-3100:00.000- Select DATEADD on the first day of the following year (YEAR, DATEDIFF (YEAR, 0, DATEADD (YEAR, 1, GETDATE()), 0) -2024-01-0100:00.000- Select DATEADD on the last day of the following year (YEAR, DATEDIFF (YEAR, 0, DATEADD (YEAR, 2, GETDATE()), -1) - - -2024-12-3100:00.000- Select DATEADD on the first day of the previous year IFF (YEAR, 0, DATEADD (YEAR, -1, GETDATE())), 0) - January 2022-0100:00.000- Last day of last year SelectDATEADD (YEAR, DATEDIFF (YEAR, 0, GETDATE()), -1) - December 2022-3100:00.000
SqlCONVERT date format conversion
SELECTCONVERT(varchar(100),GETDATE(),0)--031420235:06PMSELECTCONVERT(varchar(100),GETDATE(),1)--03/14/23SELECTCONVERT(varchar(100),GETDATE(),2)--23.03.14SELECTCONVERT(varchar(100),GETDATE(),3)--14/03/23SELECTCONVERT(varchar(100),GETDATE(),4)--14.03.23SELECTCONVERT(varchar(100),GETDATE(),5)--14-03-23SELECTCONVERT(varchar(100),GETDATE(),6)--140323SELECTCONVERT(varchar(100),GETDATE(), 7)--0314,23SELECTCONVERT(varchar(100),GETDATE(),8)--17:07:16SELECTCONVERT(varchar(100),GETDATE(),9)--031420235:07:16:800PMSELECTCONVERT(varchar(100),GETDATE(),10)--03-14-23SELECTCONVERT(varchar(100),GETDATE(),11)--23/03/14SELECTCONVERT(varchar(100),GETDATE(),12)--230314SELECTCONVERT(varchar(100),GETDATE(),13)--1403202317:07:29:713SELECTCONVERT(varchar(100),GETDATE(), 14)--17:07:43:390SELECTCONVERT(varchar(100),GETDATE(),20)--2023-03-1417:07:43SELECTCONVERT(varchar(100),GETDATE(),21)--2023-03-1417:07:43.390SELECTCONVERT(varchar(100),GETDATE(),22)--03/14/235:07:56PMSELECTCONVERT(varchar(100),GETDATE(),23)--2023-03-14SELECTCONVERT(varchar(100),GETDATE(),24)--17:07:56SELECTCONVERT(varchar(100),GETDATE(),25)--2023-03-1417:06:11.847SELECTCONVERT(varchar(100), GETDATE(),100)--031420235:06PMSELECTCONVERT(varchar(100),GETDATE(),101)--03/14/2023SELECTCONVERT(varchar(100),GETDATE(),102)--2023.03.14SELECTCONVERT(varchar(100),GETDATE(),103)--14/03/2023SELECTCONVERT(varchar(100),GETDATE(),104)--14.03.2023SELECTCONVERT(varchar(100),GETDATE(),105)--14-03-2023SELECTCONVERT(varchar(100),GETDATE(),106)--14032023SELECTCONVERT(varchar(100),GETDATE(), 107)--0314,2023SELECTCONVERT(varchar(100),GETDATE(),108)--17:08:31SELECTCONVERT(varchar(100),GETDATE(),109)--031420235:08:43:493PMSELECTCONVERT(varchar(100),GETDATE(),110)--03-14-2023SELECTCONVERT(varchar(100),GETDATE(),111)--2023/03/14SELECTCONVERT(varchar(100),GETDATE(),112)--20230314SELECTCONVERT(varchar(100),GETDATE(),113)--1403202317:08:57:650SELECTCONVERT(varchar(100),GETDATE(), 114)--17:08:57:650SELECTCONVERT(varchar(100),GETDATE(),120)--2023-03-1417:09:09SELECTCONVERT(varchar(100),GETDATE(),121)--2023-03-1417:09:09.177
That's all for this article about the summary of commonly used functions and time processing in SqlServer. For more information on commonly used functions in SqlServer, please search for previous articles or continue browsing related articles below. We hope everyone can support Script Home more in the future!
Tag: Summary of commonly used functions and time processing in
Disclaimer: The content of this article is sourced from the internet. The copyright of the text, images, and other materials belongs to the original author. The platform reprints the materials for the purpose of conveying more information. The content of the article is for reference and learning only, and should not be used for commercial purposes. If it infringes on your legitimate rights and interests, please contact us promptly and we will handle it as soon as possible! We respect copyright and are committed to protecting it. Thank you for sharing.
