What is the equivalent of ORACLE's SYSDATE in Impala?

sql oracle impala sysdate
Nazilla picture Nazilla · Jul 4, 2014 · Viewed 9.5k times · Source

I have a WHERE clause I am trying to convert from ORACLE SQL into Impala SQL. The col2 output looks something like 201406. I need to have this readable for Impala then format it then turn it into a string. Any help would be much appreciated!

WHERE Table1.col2 <= to_char( SYSDATE-7, 'YYYYMM')

Answer

Paul Maxwell picture Paul Maxwell · Jul 7, 2014

current_timestamp() is an alias of now() so either of these can be used instead of sysdate

SYSDATE-7 is sysdate minus 7 days, so the equivalent of WHERE Table1.col2 <= to_char( SYSDATE-7, 'YYYYMM') is

WHERE Table1.col2 <= concat( cast(year(to_date( days_add(current_timestamp(),-7 ))) as string), cast(month(to_date( days_add(current_timestamp(),-7 ) )) )

or if the deduction is 7 months then:

concat( cast(year(to_date( months_add(current_timestamp(),-7 ))) as string), cast(month(to_date( months_add(current_timestamp(),-7 ) )) )

note, all untested, and I have used days_add() or months_add() as many folks prefer to use a minus 7 instead of a days_sub() with positive 7. However I'm making the assumption this will work (this works in MySQL which has similar date arithmetic functions).

Also note it is implied that your field Table1.col2 is a string type, if it is an integer then a slightly different solution is needed.

year( months_add(current_timestamp(),-7 ) ) * 100 + month(days_add(current_timestamp(),-7 ))

year( months_add(current_timestamp(),-7 ) ) * 100 + month(months_add(current_timestamp(),-7 ))

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More