Redshift SQL Date functions derived as like Qlik Date functions

[Halesha_Bandri]

SentEmail:

LOAD * ;

SQL

SELECT

sent_email_datetime,

DATE_PART('month',sent_email_datetime) AS month,

DATE_PART('year',sent_email_datetime) AS year,

TRUNC(DATE_TRUNC('month',sent_email_datetime)) AS month_start_date,

LAST_DAY(sent_email_datetime) AS month_end_date,

CASE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 1 AND 3 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/01/01')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 4 AND 6 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/04/01')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 7 AND 9 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/07/01')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 10 AND 12 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/10/01')::DATE
END AS quarter_start_date,

CASE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 1 AND 3 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/03/31')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 4 AND 6 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/06/30')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 7 AND 9 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/09/30')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 10 AND 12 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/12/31')::DATE
END AS quarter_end_date,

CASE
WHEN DATE_PART('month',sent_email_datetime)=1 THEN CONCAT(DATE_PART('year',sent_email_datetime)::varchar,'/01/01')::Date
ELSE CONCAT(DATE_PART('year',sent_email_datetime)::varchar,'/01/01')::Date
END AS year_start_date,

CASE
WHEN DATE_PART('month',sent_email_datetime)=1 THEN CONCAT(DATE_PART('year',sent_email_datetime)::varchar,'/12/31')::Date
ELSE CONCAT(DATE_PART('year',sent_email_datetime)::varchar,'/12/31')::Date
END AS year_end_date

FROM "$(vConnectionPath)"."gcdw_rpt"."content_effectiveness_sent_email_vw"

LIMIT 10000
;