I am trying to compare dates below in filter as below:-
dataframe KIN_PRC_FILE has column pos_price_expiration_dt that has value 9999-12-31
val formatter = new SimpleDateFormat("yyyy-MM-dd");
val CURRENT_DATE = formatter.format(Calendar.getInstance().getTime());
val FILT_KMART_KIN_DATA= KIN_PRC_FILE.filter(s"(pos_price_expiration_dt)>=$CURRENT_DATE AND pos_price_type_cd").show(10)
but seems above query returns null records, can somebody help me to understand what is wrong here.
You just need to add single commas to your current_date variable
KIN_PRC_FILE.filter(s"pos_price_expiration_dt >= '$CURRENT_DATE'")
Quick example here
INPUT
df.show
+-----------------------+---+
|pos_price_expiration_dt| id|
+-----------------------+---+
| 2018-11-20| a|
| 2018-12-28| b|
| null| c|
+-----------------------+---+
OUTPUT
df.filter(s"pos_price_expiration_dt>='$CURRENT_DATE'").show
+-----------------------+---+
|pos_price_expiration_dt| id|
+-----------------------+---+
| 2018-12-28| b|
+-----------------------+---+
Note that you are using string comparison that has date values. Since you have it in the descending order i.e yyyy-MM-dd, this works, but not safe always.
You should consider casting the column to "date" format before doing such comparisons. And for the current date you can always use the built-in variable. Check this out:
scala> val KIN_PRC_FILE = Seq(("2018-11-01"),("2018-11-15"),("2018-11-30"),("2018-11-28"),(null)).toDF("pos_price_expiration_dt").withColumn("pos_price_expiration_dt",'pos_price_expiration_dt.cast("date"))
KIN_PRC_FILE: org.apache.spark.sql.DataFrame = [pos_price_expiration_dt: date]
scala> KIN_PRC_FILE.printSchema
root
|-- pos_price_expiration_dt: date (nullable = true)
scala> KIN_PRC_FILE.show
+-----------------------+
|pos_price_expiration_dt|
+-----------------------+
| 2018-11-01|
| 2018-11-15|
| 2018-11-30|
| 2018-11-28|
| null|
+-----------------------+
scala> KIN_PRC_FILE.filter(s"pos_price_expiration_dt >= current_date ").show
+-----------------------+
|pos_price_expiration_dt|
+-----------------------+
| 2018-11-30|
| 2018-11-28|
+-----------------------+
scala>
