T-sql: Three generic custom functions associated with date, secondary by week (Sunday is the last day of the week)

Source: Internet
Author: User
Tags date datetime functions sql net return
Function
/*
Every function has only one sentence!
In fact, from another blog I dug out:

T-SQL generates two new true Gregorian calendars
Http://blog.csdn.net/playyuer/archive/2004/04/07/2860.aspx


T-SQL generates a simple Gregorian calendar T-SQL contains the date of the month and the Year of the week
Http://blog.csdn.net/playyuer/archive/2004/04/05/2859.aspx

Due to the use of (@ @datefirst + datepart (weekday, @date))% 7 judgment Week
Therefore, it is independent of datefirst and can be adapted to various language versions of SQL Server

*/

-Sunday counts as the last day of the week (previous)

Create function Udf_weekofyear (@date datetime)
--@date Week is the first week of the year
-Sunday counts as the last day of the week (previous)
--For weekly summary group BY, no cross year data, or group by year
--group by year (date), month (date), dbo.udf_weekofyear (date), Dbo.udf_weekofmonth (date)
returns int
As
Begin
Return
(Select DateDiff (week
, case when (@ @datefirst + datepart (weekday,dateadd (Day,0,datediff day,0,dateadd (Year,datediff (year,0, @date), 0))) % 7 = 1
Then DateAdd (Day,-1,dateadd (Day,0,dateadd (Year,datediff (year,0, @date), 0)))
else DateAdd (Day,0,datediff (Day,0,dateadd (Year,datediff (year,0, @date), 0))--date The first day of the year: January 1
End
, case when (@ @datefirst + datepart (weekday, @date))% 7 = 1
Then DateAdd (day,-1, @date)
else @date
End
) + 1)
End

Go

Create function Udf_weekofmonth (@date datetime)
--Ask @date where week is the first week of the month
-Sunday counts as the last day of the week (previous)
--For weekly summary group BY, do not have a cross-month cross year data, or group by Year,month
--group by year (date), month (date), dbo.udf_weekofyear (date), Dbo.udf_weekofmonth (date)
returns int
As
Begin
Return
(Select DateDiff (week
, case when (@ @datefirst + datepart (weekday,dateadd (Month,datediff (month,0, @date), 0))% 7 = 1
Then DateAdd (Month,datediff (month,0, @date), 0)-1
else DateAdd (Month,datediff (month,0, @date), 0)
End
, case when (@ @datefirst + datepart (weekday, @date))% 7 = 1
Then @date-1
else @date
End
) + 1)
End

Go

Create function Udf_weekday (@ int, @date datetime)
returns datetime
As
Begin
/*
-Sunday counts as the last day of the week (previous)
When @ <= 1 represents mapping @date to Monday on the week
When @ = 2 represents the mapping of the @date to the week of Tuesday
When @ = 3 represents the mapping of the @date to Wednesday on the same week
When @ = 4 represents the mapping of the @date to the week of Thursday
When @ = 5 represents the @date map to the Friday of the week on which the
When @ = 6 represents the @date map to the Saturday of the week on which the
When @ >= 7 represents a map of the @date to Sunday on the week
Can be used to summarize Group by weekly, which supports cross year data across months
*/

Return
(select-@date, Datename (Weekday, @date), (@ @datefirst + datepart (weekday, @date))% 7,3-(@ @datefirst + DATEPART (weekday , @date))% 7,
DATEADD (Day
, case when (@ @datefirst + datepart (weekday, @date))% 7 = 0--Saturday
Then
Case @ between 1 and 6
Then @-6
Else 1
End
When (@ @datefirst + datepart (weekday, @date))% 7 = 1--Sunday (vii)
Then
Case @ between 1 and 6
Then @-7
else 0
End

When (@ @datefirst + datepart (weekday, @date))% 7 between 2 and 6-Monday to Friday
Then
Case @ between 1 and 6
Then @ + 1-(@ @datefirst + datepart (weekday, @date))% 7
Else 8-(@ @datefirst + datepart (weekday, @date))% 7
End
End
, @date))
/*
Test:

Select Date,datename (weekday,date), ' Map to: ', Dbo.udf_weekday (2,date), Datename (Weekday,dbo.udf_weekday (1,date))
From T
ORDER BY date

--===============
Set Datefirst 4
DECLARE @ int,@a int
SET @ = 1


Select Date,datename (weekday,date), (@ @datefirst + datepart (weekday,date))% 7,3-(@ @datefirst + datepart (weekday,date) )% 7,
DATEADD (Day
, case when (@ @datefirst + datepart (weekday,date))% 7 = 0--Saturday
Then
Case @ between 2 and 7
Then-(7-@)
else @
End
When (@ @datefirst + datepart (weekday,date))% 7 = 1--Sunday
Then
Case @ between 2 and 7
Then-(7-@)-1
else @-1
End

When (@ @datefirst + datepart (weekday,date))% 7 between 2 and 6
Then
Case @ between 2 and 7
Then @-(@ @datefirst + datepart (weekday,date))% 7
Else 8-(@ @datefirst + datepart (weekday,date))% 7
End


End
, date)
From D
ORDER BY date
*/

End


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.