| |
drop function find_beg_of_week
go
create function find_beg_of_week (@week_number int, @today datetime) returns datetime
as
begin
declare @start_week datetime
set @today = dateadd(wk, @week_number- datepart(wk,@today), @today)
set @start_week = dateadd(d, 1 - DATEPART(dw, @today), @today)
return @start_week
end
go
drop function find_end_of_week
go
create function find_end_of_week (@week_number int, @today datetime) returns datetime
as
begin
declare @end_week datetime
declare @start_week datetime
set @today = dateadd(wk, @week_number- datepart(wk,@today), @today)
set @start_week = dateadd(d, 1 - DATEPART(dw, @today), @today)
set @end_week = dateadd(d,6,@start_week )
return @end_week
end
go
select dbo.find_beg_of_week(1,getdate()), dbo.find_end_of_week(1,getdate())
For more examples on how to use SQL Server Date functions, DateAdd(), DateDiff(), DatePart(), GetDate() see other Siccolo articles:
- How to Find Last Day of a Month
- How to Find Number of Days in a Month
Side Note:
DateAdd() - Returns a new datetime value based on adding an interval to the specified date.
Where first parameter specifies on which part of the date to return a new value: yy(yyyy) for Year, mm(m) for Month,
dd(d) for Day etc.
For example: select dateadd(d,2,getdate()) - adds 2 days to current date and returns new date.
DatePart() - Returns an integer representing the specified datepart of the specified date.
Where DatePart( <interval>, <Date>)
Where the datepart interval parameter is one of the following:
- year,
- quarter,
- month,
- dayofyear,
- day,
- week,
- hour,
- minute,
- second or
- millisecond.
The number datepart interval parameter is an integer value for the number of dateparts to be added
to or subtracted from the date parameter.
Now depending on your needs, your application might use this function to perform mathematical calculations on a given date.
The DATEDIFF function is used to calculate the number of date and time boundaries crossed between two different dates. This function returns an integer value.
GetDate() - Returns the current system date and time
in the Microsoft� SQL Server� standard internal format for datetime values.
SQL Server provides the GetDate() function to get UTC time.
The GetDate() function returns a
datetime value that represents the current UTC time.
|
Questions? Suggestions? Concerns? - email me to [email protected]
Greg Dubinovsky � 2006