|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Monday from week and yearHi all,
when I have the script: declare @week int declare @year int set @week = 35 set @year = 2006 how can I obtain the monday of that week? result: 14/08/2006? Thanks a lot. Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519 select [dt] from dbo.calendar where Y = @year and W = @week go AMB Show quote "Luigi" wrote: > Hi all, > when I have the script: > > declare @week int > declare @year int > set @week = 35 > set @year = 2006 > > how can I obtain the monday of that week? > > result: 14/08/2006? > > Thanks a lot. > > Corrrection,
select [dt] from dbo.calendar where Y = @year and W = @week and dayname = 'Monday' go AMB Show quote "Alejandro Mesa" wrote: > Why should I consider using an auxiliary calendar table? > http://www.aspfaq.com/show.asp?id=2519 > > select [dt] > from dbo.calendar > where Y = @year and W = @week > go > > > AMB > > "Luigi" wrote: > > > Hi all, > > when I have the script: > > > > declare @week int > > declare @year int > > set @week = 35 > > set @year = 2006 > > > > how can I obtain the monday of that week? > > > > result: 14/08/2006? > > > > Thanks a lot. > > > > Luigi wrote:
Show quote > Hi all, Create a function:> when I have the script: > > declare @week int > declare @year int > set @week = 35 > set @year = 2006 > > how can I obtain the monday of that week? > > result: 14/08/2006? > > Thanks a lot. > CREATE FUNCTION dbo.Monday(@TargetDate DATETIME) RETURNS DATETIME AS BEGIN DECLARE @MondayDate DATETIME SELECT @MondayDate = WeekDate FROM ( SELECT @TargetDate - 7 AS WeekDate UNION SELECT @TargetDate - 6 AS WeekDate UNION SELECT @TargetDate - 5 AS WeekDate UNION SELECT @TargetDate - 4 AS WeekDate UNION SELECT @TargetDate - 3 AS WeekDate UNION SELECT @TargetDate - 2 AS WeekDate UNION SELECT @TargetDate - 1 AS WeekDate UNION SELECT @TargetDate AS WeekDate UNION SELECT @TargetDate + 1 AS WeekDate ) FullWeek WHERE DATEPART(wk, WeekDate) = DATEPART(wk, @TargetDate) AND DATEPART(dw, WeekDate) = 2 RETURN @MondayDate END Use it like this: SELECT dbo.Monday('8/17/2006') Hi Tracy,
my problem is that I have only 2 variables, one with the week (integer), and another with the year (integer too), so I can pass them to your function. Tracy McKibben ha scritto: Show quote > Luigi wrote: > > Hi all, > > when I have the script: > > > > declare @week int > > declare @year int > > set @week = 35 > > set @year = 2006 > > > > how can I obtain the monday of that week? > > > > result: 14/08/2006? > > > > Thanks a lot. > > > > Create a function: > CREATE FUNCTION dbo.Monday(@TargetDate DATETIME) > RETURNS DATETIME > AS > BEGIN > > DECLARE @MondayDate DATETIME > > SELECT @MondayDate = WeekDate > FROM > ( > SELECT @TargetDate - 7 AS WeekDate > UNION > SELECT @TargetDate - 6 AS WeekDate > UNION > SELECT @TargetDate - 5 AS WeekDate > UNION > SELECT @TargetDate - 4 AS WeekDate > UNION > SELECT @TargetDate - 3 AS WeekDate > UNION > SELECT @TargetDate - 2 AS WeekDate > UNION > SELECT @TargetDate - 1 AS WeekDate > UNION > SELECT @TargetDate AS WeekDate > UNION > SELECT @TargetDate + 1 AS WeekDate > ) FullWeek > WHERE DATEPART(wk, WeekDate) = DATEPART(wk, @TargetDate) > AND DATEPART(dw, WeekDate) = 2 > > RETURN @MondayDate > > END > > Use it like this: > SELECT dbo.Monday('8/17/2006') > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Luigi wrote:
> Hi Tracy, Sorry, I completely overlooked that in your original post. The calendar > my problem is that I have only 2 variables, one with the week > (integer), and another with the year (integer too), so I can pass them > to your function. > table method would probably be best in this case... Week 35 and year 2006 will not get August 14. August 14 is week 33.
Anyway, calendar tables are great, and might be easier to understand, but are not required. As it happens, day "zero" for SQL Server, 1 January 1900, was a Monday. That makes this a bit simpler than it otherwise would be: declare @week int declare @year int set @week = 35 set @year = 2006 select dateadd(week, datediff(week,0, dateadd(week,@week-1, dateadd(year,@year-1900,0))), 0) ------------------------------------------------------ 2006-08-28 00:00:00.000 What it does is figure out how many weeks it has been since Monday, 1 January 1900, and then add that number of weeks to that date. Roy Harvey Beacon Falls, CT Show quote On 17 Aug 2006 07:01:34 -0700, "Luigi" <ciu***@inwind.it> wrote: >Hi all, >when I have the script: > >declare @week int >declare @year int >set @week = 35 >set @year = 2006 > >how can I obtain the monday of that week? > >result: 14/08/2006? > >Thanks a lot. Roy Harvey wrote:
Show quote > Week 35 and year 2006 will not get August 14. August 14 is week 33. Brilliant!> > Anyway, calendar tables are great, and might be easier to understand, > but are not required. As it happens, day "zero" for SQL Server, 1 > January 1900, was a Monday. That makes this a bit simpler than it > otherwise would be: > > declare @week int > declare @year int > set @week = 35 > set @year = 2006 > > select dateadd(week, > datediff(week,0, > dateadd(week,@week-1, > dateadd(year,@year-1900,0))), > 0) > > ------------------------------------------------------ > 2006-08-28 00:00:00.000 > > What it does is figure out how many weeks it has been since Monday, 1 > January 1900, and then add that number of weeks to that date. >  |
|||||||||||||||||||||||
Other interesting topics