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

How to get the number of the corresponde day of the week, lest say 1,2,3,4,5,6 or 7?
LVL 1
rafaelrglAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
David ToddConnect With a Mentor Senior DBACommented:
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
 
ScobberCommented:
datepart(dw,getdate())

HTH
Scobber
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
esolveConnect With a Mentor Commented:
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
 
Patrick MatthewsConnect With a Mentor Commented:
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

Open in new window

0
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
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

Open in new window

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
 
rafaelrglAuthor Commented:
thanks guys.
0
All Courses

From novice to tech pro — start learning today.