Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQl - day of week - function

Posted on 2007-10-08
10
Medium Priority
?
5,144 Views
Last Modified: 2011-08-18
Hi,

Is there a better way of writting this stored proc?

CREATE        FUNCTION fn_getDayofWeek(@dayNbr Int)
RETURNS varchar(5)
AS  
BEGIN
 
 DECLARE @dayStr varchar(3)

If @dayNbr = 1
begin
Set @dayStr =   'Mon'
end
else if @dayNbr = 2
begin
Set @dayStr =   'Tues'
end  
else if @dayNbr = 3
begin
Set @dayStr =   'Wed'
end  
else if @dayNbr = 4
begin
Set @dayStr =   'Thurs'
end  
else if @dayNbr = 5
begin
Set @dayStr =   'Fri'
end  
else if @dayNbr = 6
begin
Set @dayStr =   'Sat'
end  
else if @dayNbr = 7
begin
Set @dayStr =   'Sun'
end  

  RETURN @dayStr
END
Thanks
0
Comment
Question by:shmz
10 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20037394
CREATE        FUNCTION fn_getDayofWeek(@dayNbr Int)
RETURNS varchar(3)
AS  
BEGIN
RETURN LEFT(CONVERT(varchar, CAST(@dayNbr AS varchar(1)) + ' Oct 2007', 109), 3)
END
0
 
LVL 17

Accepted Solution

by:
Daniel Reynolds earned 1000 total points
ID: 20037402
declare @daynum int
set @daynum = 3

SELECT CASE @daynum
            WHEN 1 THEN 'Sun'
            WHEN 2 THEN 'Mon'
            WHEN 3 THEN 'Tue'
            WHEN 4 THEN 'Wed'
            WHEN 5 THEN 'Thur'
            WHEN 6 THEN 'Fri'
            WHEN 7 THEN 'Sat'
            END
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20037562
There is an indecent simple way to do this without even think of a stored procedure by using the DATENAME function in SQL. If you habe any date you can grabb am the different parts names of it:

SELECT DATENAME(qq, getdate()) AS 'quarter' -- gives you the quarter of the year of the given date
SELECT DATENAME(dy, getdate()) AS 'day number' -- day in the year of the given date
SELECT DATENAME(wk, getdate()) AS 'week number' -- week of the year of the given date
SELECT DATENAME(dw, getdate()) AS 'week day' -- day in the week of the given date

in your stotred procrdure you can replace all the case construct with simply:

SELECT DATENAME(dw, @dayNbr-1) AS 'week day'


but the thing is that you dont actualy need a stored proc for this because you can either use

SELECT DATENAME(dw, datefield) AS 'week day' -- day in the week of the given date

or

SELECT DATENAME(dw, daynumber-1) AS 'week day' -- day week
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 20037638
Of course the examples with getdate() give you the results for today.

Related to this:

SELECT @@DATEFIRST -- (1 = Monday, 2 - Tuesday,...,7 -  Sunday)
returns you the setting for your server for the fisrt day in the week and you can change that with:

SET DATEFIRST number  -- (number: 1 = Monday, 2 - Tuesday,...,7 -  Sunday)
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20047515
I can't believe my eyes, what happened here? Are you just accepting answers without even thinking? Considering your question:

"Is there a better way of writting this stored proc?" How could you possibly choose this:

declare @daynum int
set @daynum = 3

SELECT CASE @daynum
            WHEN 1 THEN 'Sun'
            WHEN 2 THEN 'Mon'
            WHEN 3 THEN 'Tue'
            WHEN 4 THEN 'Wed'
            WHEN 5 THEN 'Thur'
            WHEN 6 THEN 'Fri'
            WHEN 7 THEN 'Sat'
            END
back to top


over this:

declare @daynum int
set @daynum = 3

SELECT left(DATENAME(dw, @dayNbr-1) ,3)


Which one is the better way? The result is exactly the same!!!! You don't even need a stored procedure for this. HGere is the questioon from me:

How are you going to use a stored procedure like that? Give me an example, please.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20047525
Oh, I just noticed the question says is a stored procedure but is a function actually. So you're going to use a user defined function instead of using thr built in functions in SQL for the same purpose. Nice.
0
 
LVL 7

Expert Comment

by:RBertora
ID: 25088202
Zberteoc : Just used your solution, its definately the best one, but I must admit if you hadn't posted your final post I would also have dismissed it.

In your first reply, it seems as if you are trying to tell me something we may already know we know about the DATENAME function, and assume it works on Date/DateTime fields (getdate()) for example.

further down the same post you have this line:
SELECT DATENAME(dw, @dayNbr-1) AS 'week day'

The problem is when I first read this I assumed @dayNbr to be a date field (not int!!!), hence discarded your solution, and I can see how this may have also happened to the guy who originally asked the question.

for the record, your solution is great, and thanks for sharing it.
Rob.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 25102395
The DATENAME function works with both dates and integer as the second parameter! What actually does gets the date, which internally is an integer, makes a modulo for the week to get the day order based on the settings described in my post ID: 20037638 and then converts it into the week day name.

You can try this and you will get Friday for both:

SET DATEFIRST 1
SELECT DATENAME(dw, '20090814') 'week day1', DATENAME(dw, 4) AS 'week day2'

I still don't understand how could you ignore my solution and my posts which are far more useful than the accepted answer. At least you could split the points. I think you didn't understand and you still don't what's the issue here.

Well, it happens.

Cheers.
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 25102468
the accepted answer was probably just more understandable for a newbie to accept.

Your answer is more concise and your attitude is more arrogant as well...

I would give the points to the less arrogant dude. But hey, this is a couple years old, so time to move on.

it does happen.

Cheers
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 25103519
I am kind of confused here: who is shmz, the author, who is RBertora and who the hack is xDJR1875? You people if you don't have any interest in the matter then better smind your business, nobody asked your opinion, I was only talking initially with the author, that is shmz.

If you don't understand SQL then better listen to the ones who do. That's the only way to learn and not by reasoning with your empty guts.

How is that for arrogance, dude?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question