CODEWITHSUNDEEP
sqlamazon-web-servicesdateamazon-athena
Chique_Code
Chique_Code

Reputation: 1530

SQL Athena getting the data for the past 12 months

I need to grab the data for the past 12 months. I am using SQL within AWS Athena. Below is my code:

CREATE
OR REPLACE VIEW response_view AS
SELECT
    "cust"."customer_id",
    "cust"."event_triggered_date"
FROM
    (
        db.population_view pop
        INNER JOIN new_db.manual_response_view cust ON ("pop"."customer_id" = "cust"."customer_id")
    )
    WHERE "cust"."event_triggered_date" > current_date - interval '12' month

Gives me an error: cannot be applied to varchar, date

event_triggered_filed is a string

This is the structure of the date field: 2019-12-04 00:00:00.000

Upvotes: 0

Views: 3731

Answers (2)

Tam
Tam

Reputation: 3987

I had same problem I need to get the stating day of the 6th and 12th month from current date.

I have written this, it works for me I hope it will work for other as well.

please refer attached image for query result

select current_date as today, date_add('month', -6, (select date(date_format(cast(current_date as date),'%Y-%m-01')))) as Start_of_the_date_6_month_before

enter image description here

For Staring day of the 12 month before date.

select current_date as today, date_add('month', -12, (select date(date_format(cast(current_date as date),'%Y-%m-01')))) as Starting_day_of_the_month_12_before

Finally i make this changes like this : enter image description here

With
six_month_And_minus_1_day AS (
select date_add('month', -6, (select date(date_format(cast(current_date as date),'%Y-%m-01')))) as Start_of_the_date_6_month_before,
date_add('day', -1,  date_add('month', -6, (select date(date_format(cast(current_date as date),'%Y-%m-01'))))) as Start_of_the_date_6_month_before_minus_1
)
select * from six_month_And_minus_1_day

Any one has better way to do this please suggest.

Upvotes: 1

ismetguzelgun
ismetguzelgun

Reputation: 1105

Try this.

CAST(EVENT_TRIGGERED_DATE AS DATE)

OR

CAST(EVENT_TRIGGERED_DATE AS TIMESTAMP )

Data Types

Upvotes: 2

Related Questions