Weekday - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Weekday

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-05-04 : 16:30:34

Hello there,

Is there a function out here that gives you weekday based on the start of week as parameter? I do not want to do the SET DATEFIRST poopoo.

Thanks

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-04 : 17:31:17

What do you mean by weekday?

Can you please provide examples to make it clear what you are looking for?

CODO ERGO SUM

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-05-04 : 18:08:42

Michael,

Thanks for the response. I have looked up and down in this forum and in all of your date related scripts but I am unable to find the weekday I need, not one forced on me by SQL server 2005.

Ok, I am talking about the weekday when used with DATEPART ( weekday, GETDATE()). Out of the box , vanilla, it gives me back Sunday. But my business rules states that the week starts on a Saturday. DATEPART is US Centric, it assumes all days start on Sunday? Even in the US some factories for example want their week to start on a Saturday for shipping purposes and all. You can work on items on Saturday and ship it on Monday. By doing so you have good OTD history.
What I need is a sort of DATEPART function that takes @WEEK_START_DAY as a function like all the function goodies you have created, which by the way I use heavily in all my code and thank you for them immensely!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-05 : 09:57:51

It's too bad that you didn't provide an example of what you are looking for, like I asked. I'm still not sure what your are trying to get.

If you are looking for the first day of a week, this shows how to get it:
Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

CODO ERGO SUM

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-05-05 : 12:44:47

Sorry about that MVJ. Here is an example. Take 05/05/2009. If I use DATEPART ( weekday, 05/05/2009) I get back 2. The result I want is 3 because my weekday starts on Saturday, but I do not want to use SET DATEFIRST at all.

Thanks for your patience

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-05 : 17:21:15

I'm not sure how you get 3 for the day of week for 2009-5-5 if the week starts on Saturday; it's a Tuesday, so wouldn't the day of week number be 4? Assuming the day of week is numbered from 1 to 7, not 0 to 6.

This code shows how to get the day of week number for any day of the week that you want to start with.

select
	[DATE] = convert(varchar(10),a.DATE,121),
	Day_Num_Start_Sat = datediff(dd,-53685,a.DATE)%7+1,
	Day_Num_Start_Sun = datediff(dd,-53684,a.DATE)%7+1,
	Day_Num_Start_Mon = datediff(dd,-53690,a.DATE)%7+1,
	Day_Num_Start_Tue = datediff(dd,-53689,a.DATE)%7+1,
	Day_Num_Start_Wed = datediff(dd,-53688,a.DATE)%7+1,
	Day_Num_Start_Thu = datediff(dd,-53687,a.DATE)%7+1,
	Day_Num_Start_Fri = datediff(dd,-53686,a.DATE)%7+1
from
	( -- Test Data
	select	DATE = convert(datetime,'20090502') union all
	select	DATE = convert(datetime,'20090503') union all
	select	DATE = convert(datetime,'20090504') union all
	select	DATE = convert(datetime,'20090505') union all
	select	DATE = convert(datetime,'20090506') union all
	select	DATE = convert(datetime,'20090507') union all
	select	DATE = convert(datetime,'20090508') union all
	select	DATE = convert(datetime,'20090509')
	) a

Results:


DATE       Day_Num_Start_Sat Day_Num_Start_Sun Day_Num_Start_Mon Day_Num_Start_Tue Day_Num_Start_Wed Day_Num_Start_Thu Day_Num_Start_Fri 
---------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- 
2009-05-02 1                 7                 6                 5                 4                 3                 2
2009-05-03 2                 1                 7                 6                 5                 4                 3
2009-05-04 3                 2                 1                 7                 6                 5                 4
2009-05-05 4                 3                 2                 1                 7                 6                 5
2009-05-06 5                 4                 3                 2                 1                 7                 6
2009-05-07 6                 5                 4                 3                 2                 1                 7
2009-05-08 7                 6                 5                 4                 3                 2                 1
2009-05-09 1                 7                 6                 5                 4                 3                 2

(8 row(s) affected)

CODO ERGO SUM

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-05-06 : 12:15:59

MVJ

I am sorry I meant to say 4. I wish the DATEPART function had an optional 3rd parameter that you can specify the WeekStartDate.
But anyways you have answered my question.
Thanks a lot

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources