Populate variable using datadiff in pyspark

Question

I'm a long time T-SQL user and am new to python. I inherited a project where one of my processes hard coded a number rather than making it dynamic. The value in the variable is nothing more than the number of months between two dates. After assigned to the variable, the integer is used in a later calculation. The problem I have is I have only found solutions to use months_between() in a dataframe. While the value is calculated correctly, the downstream process requires the integer as an input and not reading the dataframe.

In SQL, I would have written:

DECLARE varMonths
SET varMonths = SELECT DATDIFF(mm, date1, date2)

In Python I tried:

elig_endnum2 = spark.sql("select round(months_between(current_date(), date('2007-01-01')),0)")

If someone could provide me a little direction and a link to a resource on the appropriate way to solve this, I'd be grateful.

Please check the answer here, it may help: https://stackoverflow.com/questions/52527571/datediff-in-spark-sql/52582284 — kirtan_shah, Feb 14 '22 at 14:18

score 0 · Accepted Answer · answered Feb 14 '22 at 14:17

0

input_1 = '2022-02-14'
input_2 = '2007-01-01'

df = spark.createDataFrame([(input_1, input_2)], ['date1', 'date2'])

datadiff = int(df.select(months_between(df.date1, df.date2).alias('months')).collect()[0][0])

datadiff

Out[32]: 181

answered Feb 14 '22 at 14:17

Luiz Viola

2,143
1
11
30

Thanks @Luiz, exactly what I was looking for and I see where I went wrong. – Michael Gilman Feb 14 '22 at 15:11

Populate variable using datadiff in pyspark

1 Answers1