Classic SQL user-defined functions

Classic SQL user-defined functions

1. determine the number of days in a month of a year

Implementation principle: first use DATEDIFF to get the first day of the current month, then add a month to get the first day of the next month, then subtract 1 minute, and then get the date

The number of days, that is, the maximum date of the month, that is, the number of days of the month

Create function DaysInMonth (@ date datetime) Returns int

AS

BEGIN

RETURN Day (dateadd (mi,-3, DATEADD (m, DATEDIFF (m, 0, @ date) + 1, 0 )))

END

Call example:

Select dbo. DaysInMonth ('2017-02-03 ')

(2) Calculate the day of the week

Select dateadd (week, DATEDIFF (week, '2017-01-01 ', getdate (), '2017-01-01') -- returns 00:00:00. 000

Or

Select dateadd (week, DATEDIFF (week, 0, getdate (), 0)

(3) The first day of the current quarter

Select dateadd (quarter, DATEDIFF (quarter, 0, getdate (), 0)-returns 00:00:00. 000

(4) How many days of a quarter

Declare @ m tinyint, @ time smalldatetime

Select @ m = month (getdate ())

Select @ m = case when @ m between 1 and 3 then 1

When @ m between 4 and 6 then 4

When @ m between 7 and 9 then 7

Else 10 end

Select @ time = datename (year, getdate () + '-' + convert (varchar (10), @ m) + '-01'

Select datediff (day, @ time, dateadd (mm, 3, @ time)-return 92