April 9, 2013 at 1:30 pm
How can I get Saturday's date given the week number?
This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I need to get Saturday's date from this week.
April 9, 2013 at 1:36 pm
NineIron (4/9/2013)
How can I get Saturday's date given the week number?This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I need to get Saturday's date from this week.
And just what is ApptDt2? Your select appears to be selecting from a table but we don't have the table.
April 9, 2013 at 1:38 pm
You could do some ugly manipulation to create the date.
Or, you could create a calendar table and simply query it.
This is one article on nthis site; there are others.
http://www.sqlservercentral.com/articles/T-SQL/70482/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 9, 2013 at 1:39 pm
Oops. Sorry.
Use getdate() instead of ApptDt. So, what is Saturday's date, given the week number?
April 9, 2013 at 1:40 pm
There's got to be something elegant out there.
April 9, 2013 at 1:47 pm
This help?
SELECT
DATEPART(WEEK, DATEADD(MONTH, +3, getdate())),
dateadd(week,datediff(week,0,getdate()),5),
DATEADD(MONTH, +3, dateadd(week,datediff(week,0,getdate()),5));
April 9, 2013 at 1:51 pm
Oops. Sorry.
Use getdate() instead of ApptDt. So, what is Saturday's date, given the week number?
This will change year to year. What year do you want?
--EDIT--
Bitten by the quote bug.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2013 at 1:54 pm
I'll supply the year through a variable but, let's start with 2013.
April 9, 2013 at 1:56 pm
NineIron (4/9/2013)
I'll supply the year through a variable but, let's start with 2013.
Okay, now I am totally lost. Given a specific date (getdate() in this case), return the week number 3 months later and then get the Saturday of that week. Where does specifying a specific year come in to play here?
April 9, 2013 at 2:03 pm
Yea, don't think my code works quite right. Use a calendar table like previously suggested.
April 9, 2013 at 2:10 pm
How about this... create a function from this code supply a year, a week number you want. DayNumber would be=7 for Saturday.
declare @YearNum int,
@WeekNum int,
@DayNum int,
@FirstDayYear As Date
select @YearNum=2013,@WeekNum=28,@DayNum =7
SET @FirstDayYear='01/01/' + CAST(@YearNum As varchar)
select dateadd(d,(@DayNum-datepart(weekday,@FirstDayYear)),dateadd(week, @WeekNum-1,@FirstDayYear)) AS SaturdayDate
Oh well here it is:
SELECT dbo.GetMeTheDameSaturday(2013,DATEPART(WEEK, DATEADD(MONTH, +3, GETDATE())),7)
alter function dbo.GetMeTheDameSaturday(@YearNum int,@WeekNum int,@DayNum int)
returns Date as
begin
declare @FirstDayYear Date;
SET @FirstDayYear='01/01/' + CAST(@YearNum As varchar);
return dateadd(d,(@DayNum-datepart(weekday,@FirstDayYear)),dateadd(week, @WeekNum-1,@FirstDayYear))
end
April 9, 2013 at 2:18 pm
Lynn Pettis (4/9/2013)
Use a calendar table like previously suggested.
personally I like calendar tables...you can create what ever periods you need to match business requirements
here is a simple example :
use [tempdb]--==== start in safe place!!!
GO
--====Conditionally delete tables from [tempdb}
IF OBJECT_ID('tempdb..Tally', 'U') IS NOT NULL DROP TABLE tempdb..Tally ;
IF OBJECT_ID('tempdb..Calendar', 'U') IS NOT NULL DROP TABLE tempdb..Calendar ;
--==== Create a Tally table and a Calendar table
SELECT TOP 60000 IDENTITY(INT, 1, 1) AS N
INTO dbo.Tally
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
SET DATEFORMAT DMY
DECLARE @Date_Start AS DATETIME
DECLARE @Date_End AS DATETIME
SET @Date_Start = '01/01/1900'
SET @Date_End = '31/12/2030'
CREATE TABLE dbo.Calendar
(
calendar_date_ID INT IDENTITY(1, 1) NOT NULL,
calendar_week_ID INT,
calendar_date DATETIME PRIMARY KEY CLUSTERED,
calendar_year SMALLINT,
calendar_month TINYINT,
calendar_day TINYINT,
calendar_quarter TINYINT,
first_day_in_month DATETIME,
last_day_in_month DATETIME,
day_of_week TINYINT,
week_of_year TINYINT,
days_in_month TINYINT,
day_of_year SMALLINT,
is_weekday INT,
day_name VARCHAR (10),
month_name VARCHAR (10),
iso_date CHAR (8),
fiscal_year SMALLINT,
fiscal_month TINYINT
);
INSERT INTO dbo.Calendar
(calendar_date)
SELECT t.N - 1 + @Date_Start
FROM dbo.Tally t
WHERE t.N - 1 + @Date_Start <= @Date_End
UPDATE dbo.Calendar
SET calendar_week_ID = calendar_date_id / 7 + 1,
calendar_year = Datepart (YEAR, calendar_date),
fiscal_year = CASE
WHEN Datepart(M, calendar_date) >= 10 THEN Datepart (YEAR, calendar_date) + 1
ELSE Datepart (YEAR, calendar_date)
END,
calendar_month = Datepart (MONTH, calendar_date),
fiscal_month = CASE
WHEN Datepart(M, calendar_date) >= 10 THEN Datepart(M, calendar_date) - 9
ELSE Datepart(M, calendar_date) + 3
END,
calendar_day = Datepart (DAY, calendar_date),
calendar_quarter = Datepart (QUARTER, calendar_date),
first_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date), 0),
last_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1,
day_of_week = Datepart (WEEKDAY, calendar_date),
week_of_year = Datepart (WEEK, calendar_date),
day_of_year = Datepart (DAYOFYEAR, calendar_date),
is_weekday = Isnull (( CASE
WHEN ( ( @@DATEFIRST - 1 ) + ( Datepart (WEEKDAY, calendar_date) - 1 ) )%7 NOT IN ( 5, 6 )
THEN 1
END ), 0),
day_name = Datename (WEEKDAY, calendar_date),
month_name = Datename (MONTH, calendar_date),
iso_date = CONVERT(CHAR(8), calendar_date, 112),
days_in_month = Datepart(dd, ( Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1 ))
--SELECT TOP 1000 *
--FROM date_calendar
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 9, 2013 at 2:22 pm
And then, are you really talking about the calendar week or the ISO Week?
April 9, 2013 at 2:28 pm
Lynn Pettis (4/9/2013)
This help?
SELECT
DATEPART(WEEK, DATEADD(MONTH, +3, getdate())),
dateadd(week,datediff(week,0,getdate()),5),
DATEADD(MONTH, +3, dateadd(week,datediff(week,0,getdate()),5));
The calander table is about as elegant as it gets. KISS!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 9, 2013 at 2:29 pm
Lynn Pettis (4/9/2013)
NineIron (4/9/2013)
I'll supply the year through a variable but, let's start with 2013.Okay, now I am totally lost. Given a specific date (getdate() in this case), return the week number 3 months later and then get the Saturday of that week. Where does specifying a specific year come in to play here?
I think the problem here is that the OP didn't provide very good details. I think they have the week number in a table as an int and now they want to determine what the date for Saturday is of that week. You have to have the year for this to make any sense.
More than anything else we are all guessing because the OP has never really clarified EXACTLY what they are trying to do here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply