SQL date and date weekend month and month functions

The code is as follows:

Copy code

Select Dateadd (wk, datediff (wk, 0, getdate (), 0) -- Monday Select Dateadd (wk, datediff (wk, 0, getdate (), 6) -- Sunday Select Dateadd (mm, datediff (mm, 0, getdate (), 0) -- Early month Select Dateadd (MS,-3, dateadd (mm, datediff (m, 0, getdate () +) -- end of month Select Dateadd (yy, datediff (yy, 0, getdate (), 0) -- Early Year Select Dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate () + 1, 0) -- end of year

1) The start date of this year

The code is as follows:

Copy code

Create function GetYearFirstDay (@ yourDate varchar (10) -- date of the current year RETURNS varchar (10) AS BEGIN Declare @ getDate datetime, @ getDateYear int, @ returnDate datetime Set @ getDate = cast (@ yourDate as datetime) Set @ getDateYear = year (@ getDate) -- input date year Set @ returnDate = cast (str (@ getDateYear) + '-' + '01' + '-01' as datetime) -- date of the current year Return convert (varchar (10), @ returnDate, 120) END

2) the same period of the previous year date is calculated based on the last day.

The code is as follows:

Copy code

Create function GetPreYearSameDay (@ yourDate varchar (10) -- The last day of the same period of the previous year. RETURNS varchar (10) AS BEGIN Declare @ getDate datetime, @ getDateYear int, @ getDateMonth int, @ getDateDay int, @ returnDate datetime, @ returnDateYear int, @ returnDateMonth int, @ returnDateDay int Set @ getDate = cast (@ yourDate as datetime) Set @ getDateYear = year (@ getDate) -- input date year Set @ getDateMonth = month (@ getDate) -- input date month Set @ getDateDay = day (@ getDate) -- input date day Set @ returnDate = DateAdd (m, 1, @ getDate) -- get the date of next month Set @ returnDate = cast (str (year (@ returnDate)-1) + '-' + str (month (@ returnDate) + '-' + '01' as datetime) -1 -- get the date of the last day of the same month last year Set @ returnDateYear = year (@ returnDate) -- year of the same month last year Set @ returnDateMonth = month (@ returnDate) -- Last year same month Set @ returnDateDay = day (@ returnDate) -- Last day of the same month last year If @ getDateDay> @ returnDateDay -- the number of days of the input date exceeds the last day of the same month of last year. Begin Set @ returnDate = @ returnDate End Else Begin Set @ returnDate = cast (str (@ returnDateYear) + '-' + str (@ returnDateMonth) + '-' + str (@ getDateDay) as datetime) End Return convert (varchar (10), @ returnDate, 120) END

3) get the date of the previous day

The code is as follows:	Copy code
Create function GetPreSomeDate (@ yourDate varchar (10), @ PreDays int) -- get the date of the previous few days RETURNS varchar (10) AS BEGIN Declare @ getDate datetime, @ returnDate datetime Set @ getDate = cast (@ yourDate as datetime) Set @ returnDate = @ getDate-@ PreDays Return convert (varchar (10), @ returnDate, 120) END

4) the last day is the last day.

The code is as follows:

Copy code

Create function GetPreMonthSameDay (@ yourDate varchar (10) -- The last day of the same period of the previous month. RETURNS varchar (10) AS BEGIN Declare @ getDate datetime, @ getDateYear int, @ getDateMonth int, @ getDateDay int, @ returnDate datetime, @ returnDateYear int, @ returnDateMonth int, @ returnDateDay int Set @ getDate = cast (@ yourDate as datetime) Set @ getDateYear = year (@ getDate) -- input date year Set @ getDateMonth = month (@ getDate) -- input date month Set @ getDateDay = day (@ getDate) -- input date day Set @ returnDate = cast (str (@ getDateYear) + '-' + str (@ getDateMonth) + '-01' as datetime)-1 -- last day of last month Set @ returnDateYear = year (@ returnDate) -- year of the last day of the previous month Set @ returnDateMonth = month (@ returnDate) -- month of the last day of the previous month Set @ returnDateDay = day (@ returnDate) -- Last day of last month If @ getDateDay> @ returnDateDay -- if the input date is later than the last day of the previous month, return the last day of the previous month. Begin Set @ returnDate = @ returnDate End Else Begin Set @ returnDate = cast (str (@ returnDateYear) + '-' + str (@ returnDateMonth) + '-' + str (@ getDateDay) as datetime) End Return convert (varchar (10), @ returnDate, 120) END

5) Last day of last month

The code is as follows:

Copy code

Create function GetPreMonthLastDay (@ yourDate varchar (10) -- Last day of last month RETURNS varchar (10) AS BEGIN Declare @ getDate datetime, @ getDateYear int, @ getDateMonth int, @ returnDate datetime Set @ getDate = cast (@ yourDate as datetime) Set @ getDateYear = year (@ getDate) Set @ getDateMonth = month (@ getDate) Set @ returnDate = cast (str (@ getDateYear) + '-' + str (@ getDateMonth) + '-01' as datetime)-1 Return convert (varchar (10), @ returnDate, 120) END

6) The following are the first date of the month, the first date of the month, and the last date of the month.


A date format SQL statement is complex

The code is as follows:

Copy code

Create function GetMonthFirstDay (@ yourDate varchar (10) -- get the date of the beginning of this month RETURNS varchar (10) AS   BEGIN Declare @ getDate datetime, @ getDateYear int, @ getDateMonth int, @ returnDate datetime Set @ getDate = cast (@ yourDate as datetime) Set @ getDateYear = year (@ getDate) -- input date year Set @ getDateMonth = month (@ getDate) -- input date month Set @ returnDate = cast (str (@ getDateYear) + '-' + str (@ getDateMonth) + '-01' as datetime) -- date of the beginning of this month Return convert (varchar (10), @ returnDate, 120) END Create function GetMonthLastDay (@ Date datetime) RETURNS datetime AS ---- End date of this month BEGIN Declare @ getDate datetime, @ getDateYear int, @ getDateMonth int, @ returnDate datetime Set @ getDate = DATEADD (mm, 1, @ date) Set @ getDateYear = year (@ getDate) Set @ getDateMonth = month (@ getDate) Set @ returnDate = cast (str (@ getDateYear) + '-' + str (@ getDateMonth) + '-01' as datetime)-1 Return @ returnDate END

 

The code is as follows:

Copy code

Select '2013' as region, Convert (varchar (30), getdate (), 100) as style Union Select '200', convert (varchar (30), getdate (), 101) Union Select '200', convert (varchar (30), getdate (), 102) Union Select '200', convert (varchar (30), getdate (), 103) Union Select '200', convert (varchar (30), getdate (), 104) Union Select '200', convert (varchar (30), getdate (), 105) Union Select '200', convert (varchar (30), getdate (), 106) Union Select '200', convert (varchar (30), getdate (), 107) Union Select '200', convert (varchar (30), getdate (), 108) Union Select '200', convert (varchar (30), getdate (), 109) Union Select '200', convert (varchar (30), getdate (), 110) Union Select '200', convert (varchar (30), getdate (), 111) Union Select '200', convert (varchar (30), getdate (), 112) Union Select '200', convert (varchar (30), getdate (), 113) Union Select '11', convert (varchar (30), getdate (), 114)