Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

how to have the number of the corresponde day of the week

Posted on 2013-01-26
Medium Priority
474 Views
How to get the number of the corresponde day of the week, lest say 1,2,3,4,5,6 or 7?
0
Question by:rafaelrgl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 35

Assisted Solution

David Todd earned 400 total points
ID: 38823420
Hi,

This is almost completely fraught with danger - it depends on a number of things such as the datefirst setting, culture etc.

Look up the datepart function:
http://msdn.microsoft.com/en-us/library/ms174420.aspx

you want something like this:
select datepart( weekday, getdate())

HTH
David
0

LVL 7

Expert Comment

ID: 38823421
datepart(dw,getdate())

HTH
Scobber
0

LVL 6

Assisted Solution

esolve earned 400 total points
ID: 38823526
select datename(dw,getdate()) -- Saturday
select datepart(dw,getdate()) -- 7

or

SELECT  CASE DATEPART(WEEKDAY,GETDATE())
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WENSDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
0

LVL 93

Assisted Solution

Patrick Matthews earned 400 total points
ID: 38824246
Scobber and esolve,

As dtodd indicated, relying on DATEPART for this is a potentially very bad practice: depending on the setting for @@DATEFIRST, you may not get the result you are looking for.  Specifically, assuming that the Asker intends Sunday = 1, then @@DATEFIRST must equal 7.

In my opinion, the safest and simplest way to test this would be to use the DATENAME function.  Assuming English for the localization...

``````SELECT CASE LEFT(DATENAME(dw, GETDATE()), 3)
WHEN 'SUN' THEN 1
WHEN 'MON' THEN 2
WHEN 'TUE' THEN 3
WHEN 'WED' THEN 4
WHEN 'THU' THEN 5
WHEN 'FRI' THEN 6
ELSE 7 END AS WeekdayIndex
``````
0

LVL 37

Assisted Solution

ValentinoV earned 400 total points
ID: 38826040
As has been said before, you need to take the DATEFIRST setting into account to make sure the same day, in example below we use Monday , is always the first day of the week.

Here's how you can do that:

``````select ((DATEPART(dw, getdate()) + @@DATEFIRST-1-1)%7)+1
``````
See following article for an explanation on that formula: Normalizing the First Day of the Week

Using above formula you'll always get a consistent day of week result, no matter what settings are in use.
0

LVL 70

Accepted Solution

Scott Pletcher earned 400 total points
ID: 38827486
Assuming you want Monday to be day #1 -- we can adjust the "0" if needed to many any day #1.

This code always works, regardless of any date/language settings, AND IT'S UNDERSTANDABLE.

DATEDIFF(DAY, 0, date_being_checked) % 7 + 1

For example:

SELECT
DATEDIFF(DAY, 0, date_being_checked) % 7 + 1
FROM (
SELECT GETDATE() AS date_being_checked UNION ALL
SELECT GETDATE() - 1 UNION ALL
SELECT '20130117'
) AS test_data
0

LVL 1

Author Closing Comment

ID: 38832136
thanks guys.
0

Featured Post

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works