CODEWITHSUNDEEP
sqlamazon-redshift
bz_2020
bz_2020

Reputation: 79

Format of Date_Trunc in SQL (Redshift)

The below works:

SELECT DATE_TRUNC('day', TIMESTAMP '2017-03-17 02:09:30')

But if I remove the "TIMESTAMP" part (as below) it doesn't. Why is this?

SELECT DATE_TRUNC('day', '2017-03-17 02:09:30')

From what I understand, the format should just be:

DATE_TRUNC('datepart', timestamp)

This simple format works in other situations..

Upvotes: 1

Views: 2441

Answers (2)

Amish Shah
Amish Shah

Reputation: 106

Try this

select date_trunc('week', to_date('28/10/2020','dd/mm/yyyy'))

It will return date of monday for current week.

Upvotes: 0

Mark Rotteveel
Mark Rotteveel

Reputation: 109252

The timestamp '2017-03-17' is a so-called timestamp-literal. The prefix timestamp is what makes this a timestamp and not a char. If you just have '2017-03-17', then it is a char-literal, which is not a timestamp, and DATE_TRUNC requires a datetime value.

Upvotes: 2

Related Questions