topic Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks in Data Engineering

i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

ahana — Wed, 10 Nov 2021 06:32:31 GMT

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

-werners- — Wed, 10 Nov 2021 09:23:26 GMT

The trick is to make sure your columns are recognized as dates, otherwise they will be evaluated as strings.

"to_date(<datecol>, <date_format>) " does exactly this.

datecol is your column containing the date values, and date_format is the format in which your existing date is formatted. In your case "MM/dd/yyyy".

This will return values of spark dateType.

When they are recognized as dates, you can start calculating with them.

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

Hubert-Dudek — Wed, 10 Nov 2021 11:17:47 GMT

Exactly as @Werner Stinckens said. Additionally you can share your file and script so we can help better.

Your screenshot looks like excel. If it is excel format please check is all fields a data format (you can change also to number as every date is number of days from 31st December 1899). If it is csv format as werners said you need to specify format because for example 6/5/2021 can crush as it can be 5th June or 6th May.

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

Sandeep — Fri, 12 Nov 2021 13:39:26 GMT

@ahana ahana try this out:

%python
 
from pyspark.sql import functions as F
res = df.withColumn("start_date", F.to_date("start", "mm/dd/yyyy")).withColumn("end_date", F.to_date("end", "mm/dd/yyyy")).withColumn("date_diff", F.datediff("end_date","start_date"))

It works:

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

jose_gonzalez — Fri, 12 Nov 2021 23:49:55 GMT

Hi @ahana ahana ,

Did any of the replies helped you solve this issue? would you be happy to mark their answer as best so that others can quickly find the solution?

Thank you

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

ahana — Mon, 15 Nov 2021 07:11:33 GMT

i tried all way but it is still showing null

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

-werners- — Mon, 15 Nov 2021 07:19:38 GMT

I notice you do not have leading zeroes in your data format,

try M/d/yyyy instead.

https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html

Maybe you have leading spaces too, so trim() might also help.

For sure this is not a bug because the date functions in Spark are rock solid.

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

ahana — Mon, 15 Nov 2021 07:54:32 GMT

the problem i am facing

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

ahana — Mon, 15 Nov 2021 08:17:39 GMT

no its nt working i tried below function

datediff(concat(

split(start_date, '/') [2],

'-',case

when split(start_date, '/') [0] < 10 then concat('0', split(start_date, '/') [0])

else split(start_date, '/') [0]

end,

'-',

case

when split(start_date, '/') [1] < 10 then concat('0', split(start_date, '/') [1])

else split(start_date, '/') [1]

end), concat(split(end_date, '/') [2],

'-',case

when split(end_date, '/') [0] < 10 then concat('0', split(end_date, '/') [0])

else split(end, '/') [0]

end,

'-',

case

when split(end_date, '/') [1] < 10 then concat('0', split(end_date, '/') [1])

else split(end_date, '/') [1]

end

) )as diff

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

ahana — Mon, 15 Nov 2021 08:19:36 GMT

no i am not satisfied with the given answer

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

-werners- — Mon, 15 Nov 2021 09:03:27 GMT

you have to use the exact format M/d/yyyy:

Re: i am trying to find different between two dates but i am getting null value in new column below are the dates in same format tryied to change the format but still it is not working is databricks

ahana — Mon, 15 Nov 2021 11:19:58 GMT

thank u thank u soooo much it works thank u😊