SQL Server queries for date operations, such as the number of days in a specified time interval, number of breaks

1. Check the working day of the specified time interval

The main difficulty is the statutory holidays, the national holidays are different every year, also involves take some, so we design a holiday table. The main fields have year, type (whether take some), holiday date. As follows:

CREATE TABLE [dbo]. [Holidays] ([ID] [int] IDENTITY (s) not null,[holiday] [datetime2] (7) NULL,--holiday Date [years] [Char] (4) NULL,--year [daytype] [int] null--type)

　　

Add good holidays and take some dates for the current year

Write a method to calculate the working days except the official holiday

　　

After executing this table-valued function plus take some day and minus the statutory holiday is the working day, you can write another stored procedure.

2, calculates the rest day for the specified date period

This, on the contrary, is Sunday plus the statutory holiday minus take some day.

We write a function

ALTER FUNCTION getrestdays (@StartTime DATETIME2, @EndTime DATETIME2) RETURNS intasbegindeclare @LegalRest INT-- Statutory Holidays declare @AdjustmentDay int--take some work hours declare @SurplusDay INT--remaining working days declare @CountDay INT--Total days SELECT @LegalRest = COUNT (0) from dbo. Holidays WHERE daytype=1 and  years=year (GETDATE ()) and MONTH (Holiday) =month (GETDATE ()) and Holiday>[email Protected] and Holiday<[email protected] SELECT  @AdjustmentDay =count (0) from dbo. Holidays WHERE daytype=2 and  years=year (GETDATE ()) and MONTH (Holiday) =month (GETDATE ()) and Holiday>[email Protected] and Holiday<[email protected] SET @SurplusDay = [dbo]. [Getworkerdays] (@StartTime, DATEADD (day,1, @EndTime))--remaining weekday SELECT @CountDay =count (0) from dbo. Timespandays (@StartTime, DATEADD (day,1, @EndTime))  --a total number of days to  calculate the total number of days in the period return @[email Protected][email Protected] @AdjustmentDayEND

　　

3. Calculate current month and month date

Month

Simple:

SELECT  CONVERT (VARCHAR (7), GETDATE (), 120) + '-01 '

　　

Month End Date

This is simple: the first day of the month plus January minus one day

SELECT   DATEADD (day,-1, DATEADD (month,1, CONVERT (VARCHAR (7), GETDATE (), 120) + '-01 ')

　　

SQL Server queries for date operations, such as the number of days in a specified time interval, number of breaks