Get day of week in SQL Server 2005 2008

Question

If I have a date 01 01 2009  I want to find out what day it was e g  Monday  Tuesday  etc     Is there a built-in function for this in SQL Server 2005 2008  Or do I need to use an auxiliary table

User · Answer

You can use DATEPART dw  GETDATE    but be aware that the result will rely on SQL server setting   DATEFIRST value which is the first day of week setting  In Europe default value 7 which is Sunday    If you want to change the first day of week to another value  you could use SET DATEFIRST but this may affect everywhere in your query session which you do not want   Alternative way is to explicitly specify the first day of week value as parameter and avoid depending on   DATEFIRST setting  You can use the following formula to achieve that when need it    DATEPART dw  GETDATE        DATEFIRST   6 -  WeekStartDay    7   1   where  WeekStartDay is the first day of the week you want for your system  from 1 to 7 which means from Monday to Sunday    I have wrapped it into below function so we can reuse it easily   CREATE FUNCTION  dbo   GetDayInWeek   InputDateTime DATETIME   WeekStartDay INT  RETURNS INT AS BEGIN     --Note   WeekStartDay is number from  1 - 7  which is from Monday to Sunday     RETURN  DATEPART dw   InputDateTime      DATEFIRST   6 -  WeekStartDay    7   1  END   Example usage   GetDayInWeek  2019-02-04 00 00 00   1   It is equivalent to following  but independent to SQL server DATEFIRST setting     SET DATEFIRST 1 DATEPART dw   2019-02-04 00 00 00

User · Answer

Use DATENAME or DATEPART   SELECT DATENAME dw GETDATE    -- Friday SELECT DATEPART dw GETDATE    -- 6

User · Answer

EUROPE   declare  d datetime  set  d getdate    set  dow   datepart dw  d      DATEFIRST-2    7 1

User · Answer

To get a deterministic value for the day of week for a given date you could use a combination of DATEPART   and   datefirst  Otherwise your dependent on the settings on the server   Check out the following site for a better solution  MS SQL  Day of Week  The day of week will then be in the range 0 to 6  where 0 is Sunday  1 is Monday  etc  Then you can use a simple case statement to return the correct weekday name

User · Answer

If you don t want to depend on   DATEFIRST or use DATEPART weekday  DateColumn   just calculate the day of the week yourself   For Monday based weeks  Europe  simplest is   SELECT DATEDIFF day   17530101   DateColumn    7   1 AS MondayBasedDay   For Sunday based weeks  America  use   SELECT DATEDIFF day   17530107   DateColumn    7   1 AS SundayBasedDay   This return the weekday number  1 to 7  ever since January 1st respectively 7th  1753

User · Answer

With SQL Server 2012 and onward you can use the FORMAT function  SELECT FORMAT GETDATE     dddd

User · Answer

SELECT  CASE DATEPART WEEKDAY GETDATE          WHEN 1 THEN  SUNDAY       WHEN 2 THEN  MONDAY       WHEN 3 THEN  TUESDAY       WHEN 4 THEN  WEDNESDAY       WHEN 5 THEN  THURSDAY       WHEN 6 THEN  FRIDAY       WHEN 7 THEN  SATURDAY   END

User · Answer

Even though SQLMenace s answer has been accepted  there is one important SET option you should be aware of     SET DATEFIRST   DATENAME will return correct date name but not the same DATEPART value if the first day of week has been changed as illustrated below   declare  DefaultDateFirst int set  DefaultDateFirst     datefirst --  7 First day of week is  Sunday  by default select    DefaultDateFirst     DefaultDateFirst   set datefirst  DefaultDateFirst select datename dw getdate    -- Saturday select datepart dw getdate    -- 7  --  Set the first day of week to   TUESDAY    --   some people start their week on Tuesdays     set datefirst 2 select datename dw getdate    -- Saturday --  Returns 5 because Saturday is the 5th day since Tuesday  --  Tue 1  Wed 2  Th 3  Fri 4  Sat 5 select datepart dw getdate    -- 5  lt -- It s not 7  set datefirst  DefaultDateFirst

User · Answer

this is a working copy of my code check it  how to retrive day name from date in sql  CREATE Procedure  dbo   proc GetProjectDeploymentTimeSheetData    FromDate date   ToDate date  As  Begin select p ProjectName           st Time       -      et Time       as ProjectDeatils  datename dw pts StartDate  as  Day  from  ProjectTimeSheet pts  join Projects p on pts ProjectID p ID  join Timing st on pts StartTimingId st Id join Timing et on pts EndTimingId et Id where pts StartDate  gt    FromDate and pts StartDate  lt    ToDate END

User · Answer

You may find this version usefull   -- Test DATA select   datefirst create table  test  datum datetime  insert  test values   2013-01-01   insert  test values   2013-01-02   insert  test values   2013-01-03   insert  test values   2013-01-04   insert  test values   2013-01-05   insert  test values   2013-01-06   insert  test values   2013-01-07   insert  test values   2013-01-08   -- Test DATA  select  Substring  Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat            DATEPART WEEKDAY datum    datefirst-1  4 1 3  Datum         from  test

[sql] Get day of week in SQL Server 2005/2008

Examples related to sql

Examples related to sql-server

Examples related to datetime