Convert Date to Yyyymm Format

Convert date to YYYYMM format

SELECT LEFT(CONVERT(varchar, GetDate(),112),6)

Convert date string YYYY-MM-DD to YYYYMM in pandas

Might not need to go through the datetime conversion if the data are sufficiently clean (no incorrect strings like 'foo' or '001231'):

df = pd.DataFrame({'date':['1997-01-31', '1997-03-31', '1997-12-18']})

df['date'] = [''.join(x.split('-')[0:2]) for x in df.date]
#     date
#0  199701
#1  199703
#2  199712

Or if you have null values:

df['date'] = df.date.str.replace('-', '').str[0:6]

calculation between two date in YYYYMM format in Pyspark or python

You can use to_date to convert the columns to date type, and manually calculate the month difference from the year difference x 12 + month difference:

import pyspark.sql.functions as F

df2 = df.groupBy('ID','Index_month').max('Month_ID').select(
    'ID',
    (
        12 * (
            F.year(F.to_date(F.col('max(Month_ID)').cast('string'), 'yyyyMM')) - 
            F.year(F.to_date(F.col('Index_month').cast('string'), 'yyyyMM'))
        ) + 
        F.month(F.to_date(F.col('max(Month_ID)').cast('string'), 'yyyyMM')) - 
        F.month(F.to_date(F.col('Index_month').cast('string'), 'yyyyMM')) + 1
    ).alias('length')
)

df2.show()
+---+------+
| ID|length|
+---+------+
|  1|     6|
|  2|    30|
+---+------+

---

Related Topics