SQL for this week, last week, this month, the start time and end time of last month-CodePudding

Collect the SQL using time of some script, for your reference

DECLARE @ ThisWeekStartTime NVARCHAR (100), @ ThisWeekEndTime NVARCHAR (100),
this week -@ LastWeekStartTime NVARCHAR (100), @ LastWeekEndTime NVARCHAR (100), last week,
@ ThisMonthStartTime NVARCHAR (100), @ ThisMonthEndTime NVARCHAR (100), this month -
@ LastMonthSartTime NVARCHAR (100), @ LastMonthEndTime NVARCHAR (100), last month
@ LastestHalfYearStartTime NVARCHAR (100), @ LastestHalfYearEndTime NVARCHAR (100), - past
@ LastestOneYearStartTime NVARCHAR (100), @ LastestOneYearEndTime NVARCHAR (100) - nearly a year

SELECT @ ThisWeekStartTime=the CONVERT (nvarchar (10), DATEADD (wk, a DATEDIFF (wk, a 0, DATEADD (dd - 1, getdate ())), 0), 121) - this week start time
SELECT @ ThisWeekEndTime=the CONVERT (nvarchar (10), DATEADD (wk, a DATEDIFF (wk, a 0, DATEADD (dd - 1, getdate ())), 6), 121) - this week end time

SELECT @ LastWeekStartTime=the CONVERT (nvarchar (10), DATEADD (wk, a DATEDIFF (wk, a 0, DATEADD (dd - 7, getdate ())), 0), 121) - started last week time
SELECT @ LastWeekEndTime=the CONVERT (nvarchar (10), DATEADD (wk, a DATEDIFF (wk, a 0, DATEADD (dd - 7, getdate ())), 6), 121) - last week end time

SELECT @ ThisMonthStartTime=the CONVERT (nvarchar (10), dateadd (dd, - day (getdate ()) + 1, getdate ()), 121) - this month start time
SELECT @ ThisMonthEndTime=the CONVERT (nvarchar (10), dateadd (dd, - day (getdate ()), dateadd (m, 1, getdate ())), 121) - this month end time

SELECT @ LastMonthSartTime=the CONVERT (nvarchar (10), dateadd (dd, - day (dateadd (month, 1, getdate ())) + 1, dateadd (month, 1, getdate ())), 121) - last month start time
SELECT @ LastMonthEndTime=the CONVERT (nvarchar (10), dateadd (dd, - day (getdate ()), getdate ()), 121) - last month end time

SELECT @ LastestHalfYearStartTime=the CONVERT (nvarchar (10), dateadd (dd, - day (dateadd (month 6, getdate ())) + 1, dateadd (month 6, getdate ())), 121) - the past start time
SELECT @ LastestHalfYearEndTime=the CONVERT (nvarchar (10), dateadd (dd, - day (getdate ()), getdate ()), 121) - the half-year end time

SELECT @ LastestOneYearStartTime=the CONVERT (nvarchar (10), dateadd (dd, - day (dateadd (month, - 12, getdate ())) + 1, dateadd (month, - 12, getdate ())), 121) - nearly a year start time
SELECT @ LastestOneYearEndTime=the CONVERT (nvarchar (10), dateadd (dd, - day (getdate ()), getdate ()), 121) - nearly a year end time

The current time select getdate ()
The start date of the current time week (Monday, for example) the select DATEADD (week, DATEDIFF (week, 0, getdate ()), 0)
Starting last week: select dateadd (1 week, dateadd (week, DATEDIFF (week, 0, getdate ()), 0))
Starting last week: select dateadd (week, 2, dateadd (week, DATEDIFF (week, 0, getdate ()), 0))
Up and start last week: select dateadd (week 3, dateadd (week, DATEDIFF (week, 0, getdate ()), 0))

CodePudding user response:

Thanks for sharing the

CodePudding user response:

Recommend a SQL function: eomonth (), returns the date specified on the last day of the month.

 
- get this month the last 1 day 
Select 'ThisMonthEndTime=eomonth (getdate ()) 

/* 
ThisMonthEndTime 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
The 2018-12-31 

(1 row (s) affected) 
*/

CodePudding user response:

Can you don't copy? Wrong and verify