convert date in string datatype to timestamp datatype

[#1]

Hello All,

convert date in string datatype to timestamp datatype

[#2]

Hi @Rak ,

Could you please elaborate on what component you are specifically doing this (Hive, Impala, etc..) so we can properly label this question and answer accordingly?

[#3]

Dear Sir,
We are trying to merge two tables and
In this date datatype timestamp when I use the date values are shown null
and when I use date string they are shown properly
And merge operation doesn't happen.

[#4]

@Rak ,

I'm suspecting you are using Impala, because of the statement of the values returning null, but please confirm so I can properly label this question so others can see it.   Please see the following in the Impala documentation regarding the proper format for casting a string into a timestamp datatype:

https://docs.cloudera.com/documentation/enterprise/latest/topics/impala_timestamp.html

Restrictions:

If you cast a STRING with an unrecognized format to a TIMESTAMP, the result is NULL rather than an error. Make sure to test your data pipeline to be sure any textual date and time values are in a format that Impala TIMESTAMP can recognize.

Currently, Avro tables cannot contain TIMESTAMP columns. If you need to store date and time values in Avro tables, as a workaround you can use a STRING representation of the values, convert the values to BIGINT with the UNIX_TIMESTAMP() function, or create separate numeric columns for individual date and time fields using the EXTRACT() function.

Examples:

The following examples demonstrate using TIMESTAMP values with built-in functions:

select cast('1966-07-30' as timestamp);
select cast('1985-09-25 17:45:30.005' as timestamp);
select cast('08:30:00' as timestamp);
select hour('1970-01-01 15:30:00');         -- Succeeds, returns 15.
select hour('1970-01-01 15:30');            -- Returns NULL because seconds field required.
select hour('1970-01-01 27:30:00');         -- Returns NULL because hour value out of range.
select dayofweek('2004-06-13');             -- Returns 1, representing Sunday.
select dayname('2004-06-13');               -- Returns 'Sunday'.
select date_add('2004-06-13', 365);         -- Returns 2005-06-13 with zeros for hh:mm:ss fields.
select day('2004-06-13');                   -- Returns 13.
select datediff('1989-12-31','1984-09-01'); -- How many days between these 2 dates?
select now();                               -- Returns current date and time in local timezone.