Post reply

SQL Date Functions: How do I convert day name to date having the date range

  • May 29, 2019 at 4:54 pm

    #3648317

    I have start_date, end_date and attended_day. I need to convert the attended_day e.g. "Wednesday" to a data e.g. 2019-05-29 using start_date and end_date as my date range.

  • May 29, 2019 at 5:24 pm

    #3648326

    If start date is 1/1/2019 and end_date is 5/5/2019 and attended_day is Monday, which date should be used?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 29, 2019 at 6:22 pm

    #3648345 

    DECLARE @day varchar(9)
    DECLARE @end_date date
    DECLARE @start_date date

    SET @start_date = '20190525'
    SET @end_date = '20190531'
    SET @day = 'Wednesday'

    SELECT DATEADD(DAY, -DATEDIFF(DAY, day_number, @end_date) % 7, @end_date) AS date_you_want
    FROM (
        SELECT CASE @day WHEN 'Monday' THEN 0 WHEN 'Tuesday' THEN 1
            WHEN 'Wednesday' THEN 2 WHEN 'Thursday' THEN 3 WHEN 'Friday' THEN 4
            WHEN 'Saturday' THEN 5 WHEN 'Sunday' THEN 6 END AS day_number
    ) AS date_calc_1

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • May 30, 2019 at 1:54 am

    #3648473

    Hi Phil

    Thank you very much for your time. The date range is always within a week, could be 3 days or 5 days but does not go beyond 7 days.

  • May 30, 2019 at 1:56 am

    #3648474

    Hi ScottPletcher

    Thank you very much for your time. Will test solution and get back to you.

  • May 30, 2019 at 3:43 am

    #3648491

    SUPER!!!! Just tested the solution aligning it to my environment and it WORKED! Thank you very much ScottPletcher. Just a bit of background, we had a poorly designed table where attended_day was created as a multi-select based on days instead of data type date. Now we will be able to transform.

  • May 31, 2019 at 9:17 am

    #3648902

    Using an auxilary date table can solve a huge number of date related questions.

    Questions like:

    What is the number of days between two days?

    What is the number of working days between two days?

    What is the number of working hours between two days?

    What date is it 25 working days from now?

    How many working days does each year between 2000 and 2020 have.

    And plenty more date related questions.

    What is the number of days between two days.

    Your questions could be:

    select * from tempdb.dbo.SP_Calendar WHERE dt >= '20190505' and dt < '20190515' and dayname = 'Wednesday'

    If gives alle Wednesdays within the given period. (Zero if none, 1 if there is only one etc.).

     

    An example of the table could be :

    USE [tempdb]
    /****** Object: Table [dbo].[SP_Calendar] Script Date: 05/31/2019 11:05:16 ******/
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Calendar]') AND type in (N'U'))
    DROP TABLE [dbo].[SP_Calendar]
    GO

    CREATE TABLE [dbo].[SP_Calendar](
    [dt] [datetime] NOT NULL,
    [isWeekday] [bit] NULL,
    [isHoliday] [bit] NULL,
    [Holidayname] [varchar](200) NULL,
    [Y] [smallint] NULL,
    [FY] [smallint] NULL,
    [Quarter] [tinyint] NULL,
    [M] [tinyint] NULL,
    [D] [tinyint] NULL,
    [DW] [tinyint] NULL,
    [monthname] [varchar](9) NULL,
    [dayname] [varchar](9) NULL,
    [W] [tinyint] NULL,
    [spare1] [varchar](30) NULL,
    [spare2] [varchar](30) NULL,
    [spare3] [varchar](30) NULL,
    PRIMARY KEY CLUSTERED 
    (
    [dt] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    Offcourse it takes some code to fill the table, but once filled, it can serve very many purposes. One row for each day should be created (suggestion from 1860 to 2150 would be adequate for most of the purposes.)

    Most columns can be filled fairly automatic using SQL-server only.

    For holidays like Christmas it is easy. For example for easter etc. 'tables' with the dates for a large number off years can be used.

    I use the isholiday to denote that this is not a working day, I use the holidayname for the name of the day, some named holidays are still working days where I live.

    For myself I can calculate the number of hours every employee works, for each year taking in account that some employees work parttime, for example not working on a wednesday or a friday.

    All the calculations etc. can also be done with a script, but once the Auxilary table is available, most of the questions become far simpler with this table.

     

    There might be additional columns usefull to the Auxilary table. So anybody with suggestions ?

    Greetings,

    Ben

     

     

     

     

     

     

     

     

    • This reply was modified 5 years, 3 months ago by  ben.brugman. Reason: [Quarter] was written with a Q only which is a formatting command

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply