How generate days like day 1, day 2,day-3 from particular date to date in Sql-CodePudding

Hi I have a requirement lets say the below start date 14-09-2022 to 23-10-2022, these dates has convert like Day 1,Day 2, Day 3, Etc. in SQL or Big query

**Input date:**

**Date**     **Day**
14-09-2022  Day 1,
15-09-2022  Day 2,
16-09-2022  Day 3,
17-09-2022  Day 4,
18-09-2022  Day 5,
19-09-2022  Day 6,
20-09-2022  Day 7,
.   
.   
23-10-2022  Day 39,

Sample output

CodePudding user response：

I mean you can use DATE_DIFF

SELECT DATE_DIFF(DATE '2022-09-20', DATE '2022-09-13', DAY) AS days_diff;

CodePudding user response：

Try the following:

SELECT dates
  ,concat("Day ", ROW_NUMBER() OVER (ORDER BY dates)) day
FROM sample_data

The above will only work if you've pre-selected your date range via some kind of sub-select or CTE. With the sample data you provided, the following will be produced: