Convert date from integer to date format

sql convert int to date yyyymmdd
sql date stored as integer
convert integer to datetime python
sql convert int to time
convert nvarchar to datetime mm/dd/yyyy in sql
convert integer value to time in sql
convert integer to date online
convert integer to date excel

I have a column 'start_date' which is an integer 37823. This happened when I used xlrd library to convert xlsx to csv. Hence '2003/07/21' got converted to 37823.

I have gone through xlrd documentation and I understand there are several ways to convert it to date. However, I need to convert this to date format using PySpark in AWS Glue ETL jobs. Any suggestions?

I tried using to_date, date_format functions, but nothing worked.

I was able to finally resolve this issue.

Using UDF:

def convert_date(x):
    mDt = datetime.datetime(1899, 12, 30)
    dlt = mDt + datetime.timedelta(days=x)
    return dlt.strftime("%Y-%m-%d")

convert_date_udf = udf(lambda z: convert_date(z), StringType())
df = df.withColumn('hire date', convert_date_udf('hire date').alias('hire date new'))

Without using UDF:

df = df.withColumn('hire date', F.expr("date_add(to_date('1899-12-30'), cast(`hire date` as int))").cast(StringType())

Hope it helps!

SQL Server function to convert integer date to datetime format, In this tip we look at code you can use to convert an integer date into a datetime format. This is an issue you will face when working the history  As you can see, run_date is stored in the format of YYYYMMDD. It is stored as an integer format, not as a string as is the root format underlying the datetime data type within Microsoft SQL Server. I suspect it will only be a matter of time before the msdb database receives the same treatment the master database did in regards to the logical

I suppose that there are more elegant ways to done this but this is what I have come up with for now.

from datetime import date
df.hire_date = df.hire_date.apply(date.fromordinal) # this will give you date in dash format
df.hire_date = df.hire_date.apply(lambda x: str(x).replace('-', '/')) # this will simply replace dash with slash

Hope that this works for you :)

Convert INT to DATETIME (SQL), you need to convert to char first because converting to int adds those days to 1900-01-01 select CONVERT (datetime,convert(char(8)  A serial date number represents the whole and fractional number of days from a specific date and time, where datenum ('Jan-1-0000 00:00:00') returns the number 1. (The year 0000 is merely a reference point and is not intended to be interpreted as a real year in time.) Python, datetime.date.fromordinal. Return the date corresponding to the

Looks like 12/30/1899 + 37823 days = 2003/07/21

You can use this date_add function below instead of UDF or python functions. UDFs are supposed to be a little slower than pyspark functions.

weird_date = 37823
df = spark.createDataFrame([('1899-12-30',)], ['dt'])
df.select(date_add(df.dt, weird_date).alias('converted_date')).collect()

SQL convert date, How to convert sql date into different formats in T-SQL If you use the CONVERT or CAST to convert a datetime to integer, it will return the  Due to some clueless soul, we have several fields in a legacy database that uses a 6-digit integer to represent a date in (YYYYmm) format. The date field is assumed to be the last day of the given month. I have written the following method to perform the conversion from int to java.util.Date:

[Solved] Convert integer to datetime, Assuming your datetime stored in Epoch & Unix Timestamp format, please ignore if this is not the case. Hide Copy Code. declare @mydate  INPUT Function is used to convert the character variable to sas date format. FORMAT Function is used to display the SAS date values in a particular SAS date format. If we would not use format function, SAS would display the date in SAS datevalues format. For example, 20588 is a sas datevalue and it is equivalent to '14MAY2016'.

Convert string or numeric time values into date format—ArcGIS Pro , Use this tool to specify a standard or custom time format for interpreting date and time values and converting those into a date format. Standard formats are  max(a.cre_acc_paid_date)Max_Act_Paid_Date, Resulting Date format is as follows. 41327. 41318. 41320 etc. When I load data from QVD file to my QV data model I want the above number format to convert to a date format which will ultimately be linked to a master calender,

Numeric-to-DATE Conversion, Although not recommended, you can explicitly convert numbers to dates. Teradata Database stores each DATE value as a four-byte integer using the following  Supports Unix timestamps in seconds, milliseconds, microseconds and nanoseconds. #N#Human date to Timestamp. [batch convert] Input format: R FC 2822, D-M-Y, M/D/Y, Y-M-D, etc. Strip 'GMT' to convert to local time. Prefer a 12-hour clock? Go to preferences. Press c to clear all forms. Epoch dates for the start and end of the year/month/day.

Comments
  • Possible duplicate of How do I read a date in Excel format in Python?
  • Dylan - I guess its not. As I am not looking to convert it to date format during xlsx to csv conversion. I need to convert it later in the data pipeline. Is that possible?
  • Isn't this going to work for you -> stackoverflow.com/questions/2623156/… ?
  • Possible duplicate of How to convert the integer date format into YYYYMMDD?
  • @Dawid_Sielski - I used the same function like this :df = df.withColumn('hire date', datetime.fromordinal(df['hire date']).strftime('%Y%m%d')) TypeError: an integer is required
  • Thanks Lucifer. I tried something like this: df2 = spark.createDataFrame([('1899-12-30',)], ['dt']) df = df.select(date_add(df2.dt, df['hire date']).alias('converted_date')).collect() print df.head() Got this error: TypeError: 'Column' object is not callable
  • I think it's because you used "df" before you declared it.
  • Well I keep getting the error : Method date_add([class org.apache.spark.sql.Column, class java.lang.String]) does not exist. This is mycode: from pyspark.sql import functions as f df2 = spark.createDataFrame([('1899-12-30',)], ['dt']) df = df.select(f.date_add(df2.dt, 'hire date').alias('hire date'))
  • The date_add function takes (column, integer). You're trying to put (column, string).
  • The 'hire date' column is of IntegerType. However, since it has a space, I am not able to use it without '' and when I use it with '', it is interpreted as string.