CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Akshay
Akshay

Reputation: 81

Generate start and end dates in SQL from a column like 2017-M1

If a date is like 2017-M1, then i need to generate 01-01-2017 as start date and 31-01-2017 as end date using SQL.

Upvotes: 0

Views: 79

Answers (1)

Pankaj
Pankaj

Reputation: 2746

Use below to get first date and last date as per date text in question.

Below example is tested for leap year -

with date_cte (org_dt) as
(
select '2020-M2' // Put source date-string here
), splt_dt as
(
select split_part(org_dt,'-',1) as yr,
case
when split_part(org_dt,'-',2)='M1' then 01
when split_part(org_dt,'-',2)='M2' then 02
when split_part(org_dt,'-',2)='M3' then 03
when split_part(org_dt,'-',2)='M4' then 04
when split_part(org_dt,'-',2)='M5' then 05
when split_part(org_dt,'-',2)='M6' then 06
when split_part(org_dt,'-',2)='M7' then 07
when split_part(org_dt,'-',2)='M8' then 08
when split_part(org_dt,'-',2)='M9' then 09
when split_part(org_dt,'-',2)='M10' then 10
when split_part(org_dt,'-',2)='M11' then 11
when split_part(org_dt,'-',2)='M12' then 12 end mn
from date_cte
), f_date as
(
select to_date(yr||'-'||mn||'-'||01,'yyyy-mm-dd') dt from splt_dt
)
select date_trunc(month,dt) first_date, last_day(dt) last_date from f_date;
FIRST_DATE LAST_DATE
2020-02-01 2020-02-29

Following query can be added (replace existing query) after CTE portion in above query to generate month calendar -

SELECT dt + ROW_NUMBER() OVER(ORDER BY seq4())-1 AS MONTH_CAL 
  FROM f_date, 
  TABLE(GENERATOR(ROWCOUNT => (extract(day,last_day(dt)))))
  
+------------+
| MONTH_CAL  |
|------------|
| 2020-02-01 |
| 2020-02-02 |
| 2020-02-03 |
.
.
...truncated
| 2020-02-28 |
| 2020-02-29 |
+------------+

Refer to get more example for getting first day/month/year from date

Refer to get more example for getting last day of month

Output date format can be done as needed using to_varchar, refer.

to_varchar(date,'dd-mm-yyyy')

Upvotes: 1

Related Questions