Let's take a look at common date functions.
Date functions:
1. Last month: for example, the last month is 201201 :( CONVERT ([varchar] (6), dateadd (month, (-1), getdate (), (112 )))
2, yesterday: 2012-02-02 CONVERT (varchar (100), GETDATE ()-1, 23)
3. Today: 2012-02-03 CONVERT (varchar (100), GETDATE (), 23)
4, last day of last month: 23:59:59. 997 dateadd (MS,-3, DATEADD (mm, DATEDIFF (mm, 0, getdate (), 0 ))
5. On the first day of the month, 00:00:00. 000 DATEADD (mm, DATEDIFF (mm, 0, getdate (), 0)
Next let's look at the T-SQL user-defined function to return the day before or after the date
The code is as follows: |
Copy code |
-- ===================================================== ====== -- Author: Insus. NET -- Create date: 2012-03-22 -- Description: specifies the date, and returns the date of the previous day. -- ===================================================== ====== Create function [dbo]. [udf_Yesterday] (@ datetime DATETIME) RETURNS DATETIME AS BEGIN Return dateadd (DAY,-1, @ datetime) END GO
|
Return the next day:
The code is as follows: |
Copy code |
-- ===================================================== ====== -- Author: Insus. NET -- Create date: 2012-03-22 -- Description: specifies the date, and returns the date of the next day. -- ===================================================== ====== Create function [dbo]. [udf_Tomorrow] (@ datetime DATETIME) RETURNS DATETIME AS BEGIN Return dateadd (DAY, 1, @ datetime) END GO |
Instance
The code is as follows: |
Copy code |
SELECT [dbo]. [udf_Yesterday] (CURRENT_TIMESTAMP) AS [Yesterday] SELECT [dbo]. [udf_Tomorrow] (CURRENT_TIMESTAMP) AS [Tomorrow] |
Effect
Note: For better specification and code maintenance, Insus. NET often refactor some reusable code into a function.