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
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:
I mean you can use DATE_DIFF
SELECT DATE_DIFF(DATE '2022-09-20', DATE '2022-09-13', DAY) AS days_diff;