• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5165
  • Last Modified:

SQl - day of week - function

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
shmz
Asked:
shmz
1 Solution
 
Patrick MatthewsCommented:
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
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
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
 
ZberteocCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
RBertoraCommented:
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
 
ZberteocCommented:
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
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
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
 
ZberteocCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now