May 26, 2009 at 2:21 am
Dear All,
How to get the start date of the Week, Parameter will be Week number.
for Example: Currently We are running under 22nd Week of the year.
select DATEPART(week,GETDATE()) using this, we will get the Current Week,
Same Way i would like to know that What is that start date of the this week,
Please let me know.
Thanks
May 26, 2009 at 3:04 am
vkoka (5/26/2009)
Dear All,How to get the start date of the Week, Parameter will be Week number.
Hi,
Why from the parameter of the week,
because its need to another parameter of Year to get the requirement.
for that you get the result directly from current date by
Select DATEADD(Week, DATEDIFF(Week, 6,getdate()), 6)
ARUN SAS
May 26, 2009 at 3:05 am
Try this
DECLARE @WeekNumINT =3,
@YearINT = 2009,
@StartDayOfWeekDATETIME,
@FirstJanDATETIME
SELECT @FirstJan = CONVERT(DATETIME,'1Jan'+CONVERT(NVARCHAR(4),@Year))
IF (@WeekNum = 1)
BEGIN
SELECT @StartDayOfWeek = @FirstJan
END
ELSE
BEGIN
SELECT @StartDayOfWeek = @FirstJan +(7-DATEPART(dw,@FirstJan)+1)+(@WeekNum-2)*7
END
SELECT @StartDayOfWeek AS StartDayOfWeek
Hope you understood..
May 27, 2009 at 8:47 am
To get the first day of the week, I use:
SELECT DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE())
That simply subtracts the number of weekdays since the beginning of the week from the current date.
Two caveats:
1. Keep in mind that GETDATE() returns date and time. If you want your results in whole days, you'll need to trim the time part. Here's one way to do that:
SELECT CAST(CONVERT(NVARCHAR(10), GETDATE(), 110) AS DATETIME)
2. The default week in SQL (at least on my servers) is Sunday to Saturday. If you have a Monday to Sunday accounting week like I do, you need to set the DATEFIRST property to change the first day of your week.You can set it to any day you need - 1 is Monday, 2 is Tuesday, etc. Sunday is 7, not 0 (also the default in most cases).
Put it all together and you get:
SET DATEFIRST 1 --Monday to Sunday week
SELECT CAST(CONVERT(NVARCHAR(10), DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE()), 110) AS DATETIME)
Hope that helps.
May 27, 2009 at 8:55 am
Question I have, is what day of the week does your week start on? Need to know that before we can tell you how to determine the starting date of a week.
May 27, 2009 at 9:03 am
GMTA, Lynn - see #2 above.
May 27, 2009 at 9:11 am
georgew (5/27/2009)
GMTA, Lynn - see #2 above.
What?? If you are talking about your post, not sure if that truly answers my question. Is the OP using the SQL Server default? What is the default for their server, as it could be different based on the installation.
May 27, 2009 at 10:08 am
The calculation depends on the value of the system parameter @@datefirst during the session where you calculate the first day of week.
By default this is set to 7, meaning Sunday. But, as Lynn already stated, it can be set to any other value on your system or maybe even in the current session by using SET DATEFIRST.
You're stating that you're currently running under week 22.
This may be true for your business calender, but if your DATEFIRST value in the session you're running is set to 4, today's week would be 21.
SET DATEFIRST 4
select @@datefirst, datepart(week,getdate())
The question is, whether you need to know the first day of week based on the system parameter for @@datefirst or based on your business requirement (usually either 7 or 1)...
May 27, 2009 at 11:25 am
Apologies for any confusion, Lynn. I thought I had the week start day issue covered with "...you need to set the DATEFIRST property to change the first day of your week.You can set it to any day you need - 1 is Monday, 2 is Tuesday, etc. Sunday is 7, not 0 (also the default in most cases). "
While as you stated, I would need to know vkoka's week start day know I was explicitly providing the exact code for the job, I hope I provided enough info that one could tweak the SET DATEFIRST line to meet their needs without further information.
Am I missing something?
May 27, 2009 at 11:32 am
georgew (5/27/2009)
Apologies for any confusion, Lynn. I thought I had the week start day issue covered with "...you need to set the DATEFIRST property to change the first day of your week.You can set it to any day you need - 1 is Monday, 2 is Tuesday, etc. Sunday is 7, not 0 (also the default in most cases). "While as you stated, I would need to know vkoka's week start day know I was explicitly providing the exact code for the job, I hope I provided enough info that one could tweak the SET DATEFIRST line to meet their needs without further information.
Am I missing something?
My question would be this, do you really want to be setting datefirst? I think it may be better to first know what the business considers the start of the week, then build something that answers the question regardless of the server setting of DATEFIRST. Simple reasoning, what if that changes but the business definition doesn't? Or the other way around if you want.
May 27, 2009 at 11:50 am
Interesting point, and somewhat different from what I understood your original question to be. I never considered the possiblity of DW/DATEFIRST behavior changing.
However your comments now have me curious and a bit concerned as I'm working on a report that is likely to be used for several years to come that relies on the method I posted above. So, can you show me how you would you write robust code that doesn't rely on the DATEFIRST property for a Monday to Sunday week?
Thanks!
G
May 27, 2009 at 11:57 am
Realizing that this behaviour may also change in a future version of SQL Server, but is independent of the DATEFIRST setting, take a look at the date routines here: Some Common Date Routines.
May 27, 2009 at 1:28 pm
Hi Lynn,
your date routines are really helpful!
One question though:
When I try "set @ThisDate = '20090524'" I get "2009-05-25 00:00:00.000" as Beginning of this week (Monday) which is "strange" - at least from my point of view: the weeks over here in Germany start on Monday, so this Sunday would belong to week "2009-05-18 00:00:00.000". Every Sunday for @ThisDate will point to the following Monday. Is this intentionally?
The "workaround" I'm using:
select
CASE
WHEN datepart(dw,@ThisDate)+@@datefirst = 8 --always points on Sunday, regardless of @@datefirst
THEN dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0) -- switch to prev. week
ELSE dateadd(wk, datediff(wk, 0, @ThisDate), 0)
END
Edit: workaround added
May 27, 2009 at 1:55 pm
Great stuff, Lynn. Thanks!
May 27, 2009 at 1:57 pm
Imu92(5/27/2009)
Hi Lynn,your date routines are really helpful!
One question though:
When I try "set @ThisDate = '20090524'" I get "2009-05-25 00:00:00.000" as Beginning of this week (Monday) which is "strange" - at least from my point of view: the weeks over here in Germany start on Monday, so this Sunday would belong to week "2009-05-18 00:00:00.000". Every Sunday for @ThisDate will point to the following Monday. Is this intentionally?
The "workaround" I'm using:
select
CASE
WHEN datepart(dw,@ThisDate)+@@datefirst = 8 --always points on Sunday, regardless of @@datefirst
THEN dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0) -- switch to prev. week
ELSE dateadd(wk, datediff(wk, 0, @ThisDate), 0)
END
Edit: workaround added
Lutz
You can get rid of your case statement. See the following code:
declare @ThisDate datetime;
set @ThisDate = '2009-05-31';
select dateadd(wk, datediff(wk, 0, dateadd(dd, -1, @ThisDate)), 0);
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply