CODEWITHSUNDEEP
sqlamazon-redshift
324
324

Reputation: 714

How can I remove the timestamp from a date/time field in SQL?

I am joining two different tables in SQL (I am still relatively new to SQL). The first one has timestamps like 12/11/2013 23:50:12 PM and the second like 12/11/2013 0:00 (notice the difference in time formatting). They are not joining properly because of this, so I want to remove the time portion of the timestamp, and I am unsure how to do this.

I want 12/11/2013 23:50:12 PM and 12/11/2013 0:00 to be recognized as equivalent.

Note: I am using AWS Redshift.

Upvotes: 1

Views: 5265

Answers (2)

Clay Coleman
Clay Coleman

Reputation: 56

You can do a cast on the field you want as a date

cast(timestamp as date)

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If these are indeed timestamp, you can just use trunc():

on trunc(t1.timestamp) = trunc(t2.timestamp)

If they are not timestamps or dates, you can use to_date() to convert them. Or, just use the first 10 characters:

on left(t1.bogus_timestamp, 10) = left(t2.bogus_timestamp, 10)

Upvotes: 1

Related Questions