CODEWITHSUNDEEP
sqlimpala
jirislav
jirislav

Reputation: 359

How to get last day of previous month in Impala?

I'd like to get the last day of previous month in Impala as any type (preferably string).

It should be well readable and effective.

Upvotes: 1

Views: 8519

Answers (2)

dnoeth
dnoeth

Reputation: 60482

Subtract the day of month from today and you get the last day of the previous month:

date_sub(now(), day(now())

This includes the current time.

To get midnight you can truncate it to the begin of month and subtract one day:

date_sub(trunc(now(), 'month'), 1)

Both result in timestamp, but can be easily casted to a string.

Upvotes: 2

jirislav
jirislav

Reputation: 359

Using the regexp

SELECT 
    days_sub(
        regexp_replace( 
            regexp_extract(
                cast(now() AS string),
                '[\\d-]{10}',
                0
            ), /* Get today in format: YYYY-mm-dd */
            '\\d{2}$', '01'
        ), /* Get the first day of this month: YYYY-mm-01 */
        1
    ) /* Subtract one day */
    AS DAY

One liner

SELECT days_sub(regexp_replace(regexp_extract(cast(now() AS string), '[\\d-]{10}', 0),'\\d{2}$', '01'), 1) AS DAY

Using the extract & concat functions

SELECT 
    days_sub(
            concat(
                cast(extract(now(),'year') AS string), /* Extract current year*/
                '-',
                regexp_replace(
                    cast(extract(now(),'month') AS string), /* Extract current month */
                    '^\\d$', '0\\0'
                ), /* Make sure the month has two digits e.g. from '1' create '01' */
                '-01'
            ),  /* Concat current year, month and day one */
            1 
        ) /* Subtract one day */
        AS DAY

One liner

SELECT days_sub(concat(cast(extract(now(),'year') AS string), '-', regexp_replace(cast(extract(now(),'month') AS string), '^\\d$', '0\\0'), '-01'), 1) AS DAY

Comparison

Both options result in the same result of type TIMESTAMP:

2017-06-30 00:00:00

It can be casted to string easily by using cast(result as string)

The regexp way seems to be more readable to me, so I'm using that version.

Upvotes: 0

Related Questions