Convert string to date format

 If you have date in string datatype like '20230301' and  you want to convert it into 2023-03-01 in data datatype then please find below examples.


Solution 1

select process_date, to_date(process_date, 'yyyyMMdd') as new_date from table

Now you will below results:

process_date new_date
20230603         2023-06-03



Solution 2


If you want to get some prior date on base of exiting date and you date in string datatype
'YYYYMMDD' e.g 20230603         


select process_date, ,date_add(to_date(process_date, 'yyyyMMdd'),-1) as new_date
from table


Results:

process_date new_date
20230603         2023-06-02

 

Comments

Post a Comment

Popular posts from this blog

Date format issue with spark sql

 If you have date format like  date_col 1/1/1978 0:00 6/6/1975 0:00 1/1/1978 0:00 1/1/1978 0:00 7/23/1988 0:00 1/1/1978 0:00 1/26/1961 0:00 3/3/1980 0:00 6/2/1984 0:00 1/2/1971 0:00 Solution : substring(TO_DATE(date_col ,'MM/dd/yy'),0,10) from table Result : 1978-01-01 1975-06-06 .. 1988-07-23 ..
Read more

Hive Partition sub folders HIVE_UNION_SUBDIR_1,HIVE_UNION_SUBDIR_2,HIVE_UNION_SUBDIR_8

 Hive Partition have sub folders like HIVE_UNION_SUBDIR_1,HIVE_UNION_SUBDIR_2,HIVE_UNION_SUBDIR_3 Problem : When you use UNION ALL in query with Hive version 1.2.0 onward. UNION ALL will not supported and we have TEZ engine setup in hive-site.xml or hive configuration file which is responsible to create  sub folders like HIVE_UNION_SUBDIR_1 etc  on HDFS. When you use spark sql or other sql query to read Partition data then your resultant partition created with blank or 0 value. Solution: Please change the configuration file of hive and set below property. Its work for me in dataiku. hive.execution.engine=mr It will invoke Map Reduce process which will  bit slow to process the job but it will help to stop creating the extra sub folder on HDFS.  
Read more

Dataiku and Dremio date difference

In dataiku you have applied to_date function to convert string to date datatype in SparkSQL and when query same dataset in dremio then you will difference of 1 day. Example -  In dataiku      SELECT  offer_date  FROM temp      Result :      2023-05-18T00:00:00.000Z Dremio :     SELECT  offer_date  FROM temp Result :      2023-05-17 08:00:00.000 Now you will find the difference of  1 day and 8 hours (sometime 16 hours)  between Dataiku and Dremio. Solutions:       This difference is due to time difference. In Dataiku we have  correct date to correct it Dremio please use TO_TIMESTAMP function to get correct date. Example : select TO_TIMESTAMP(offer_date, 'yyyy-mm-dd' ) as offer_date from temp
Read more