SQL: Convert table with start-end dates to just dates retaining other columns

Question

I understand that a number of similar questions have been answered, but I think none seems to be specific to this? I might be wrong as I am still quite new to SQL too. Hence, appreciate if anyone can kindly explain and point me the right direction.

A sample table is as shown below, with start and end dates, and one more column with some text details. Some of the end dates are NULL.

Start Date  End Date    Day
25-05-15    28-05-15    text1
28-05-15                text2
30-05-15                text3
01-12-15                text4
31-05-16    02-06-16    text5
07-07-16                text6

I want to convert this table to a new one just showing all the days while retaining the "Day" column specific to the dates.

Date        Day
25-05-15    text1
26-05-15    text1
27-05-15    text1
28-05-15    text1
28-05-15    text2
30-05-15    text3
01-12-15    text4
31-05-16    text5
01-06-16    text5
02-06-16    text5
07-07-16    text6

Can anyone help? Thanks in advance!

You should store date values as `DATE` data type. – Paul Spiegel Aug 25 '16 at 13:10 — Paul Spiegel, Aug 25 '16 at 13:10
Hi, the date values are in date time format – Jake Aug 26 '16 at 01:29 — Jake, Aug 26 '16 at 01:29

score 1 · Accepted Answer · answered Aug 25 '16 at 13:31

If you have a helper table with all dates you would ever need (e.g. 30K dates from 1970-01-01), you can join it with your table on the given date range. This query is for MySQL:

select d.`Date`, t.`Day`
from mytable t
join `dates` d
  on  d.`Date` >= str_to_date(t.`Start Date`, '%d-%m-%y')
  and d.`Date` <= str_to_date(coalesce(`End Date`, `Start Date`), '%d-%m-%y')

http://sqlfiddle.com/#!9/2e3e1/1

You can create that helper table on the fly or store it in your db for later use. How to create that table depends on your RDBMS.

score 0 · Answer 2 · answered Aug 25 '16 at 12:02

You could try to do this within a script (this one is Oracle):

DECLARE

  CURSOR dt_cursor IS
    SELECT StartDate               AS FromDate,
           NVL(EndDate, StartDate) AS ToDate,
           Day
    FROM   myTable;

  dt_val  dt_cursor%ROWTYPE;
  my_date DATE;

BEGIN

  FOR dt_val IN dt_cursor LOOP

    my_date = dt_val.FromDate;
    WHILE (my_date <= dt_val.ToDate) LOOP

      INSERT INTO newTable (Date, Day) VALUES (my_date, dt_val.Day);
      my_date := my_date + 1;

    END LOOP;

  END LOOP;

END;

score 0 · Answer 3 · answered Aug 25 '16 at 12:19

0

Try with the below script for SQL server.

DROP TABLE #T
GO

CREATE TABLE #T
(StartDAte DATETIME,
 EndDate DATETIME,
 Day VARCHAR(50))


 INSERT INTO #T
 VALUES ('05-25-15','05-28-15','text1'), ('05-28-15',NULL,'text2')
        ,('05-30-15',NULL,'text3'),('12-01-15',NULL,'text4')
        ,('05-31-16','06-02-16','text5'),('07-07-16',NULL,'text6')

Below script will give you the result set.

 SELECT [Date],[Day] 
 FROM #T
   CROSS APPLY (SELECT DATEADD(DAY,number,StartDAte) [Date]
                FROM master..spt_values
                WHERE type = 'P'
                AND DATEADD(DAY,number,StartDAte) < =ISNULL(EndDate,StartDate))t

answered Aug 25 '16 at 12:19

Unnikrishnan R

4,965
1
12
21

Hi, I have trouble understanding the statement in the cross apply~ which table is "master..spt_values"? Where does the field "type" come from? Thanks! – Jake Aug 27 '16 at 09:51
jake, spt_values is a default system function available in sql server for manipulating the strings.. Type P is a list of consecutive numbers between 0 and 2047.if you wantded to know more just follow the link.. [link](http://stackoverflow.com/questions/4273978/why-and-how-to-split-column-using-master-spt-values) – Unnikrishnan R Aug 27 '16 at 10:06

SQL: Convert table with start-end dates to just dates retaining other columns

3 Answers3