Presto SQL - Converting a Date String to Date Format

How to convert string 'yyyy-mm-dd' to date format in presto sql

You can use date function, which is a shortcut for CAST(x AS date).

presto> SELECT date('2020-06-10');
   _col0
------------
 2020-06-10

Convert string date format MM/dd/yyyy HH:mm:ss.SSSS to timestamp presto

The value in your example matches the format of timestamp literals in SQL, so you can convert it to a timestamp type by just casting the value to the desired type:

> select cast('2022-03-30 17:18:09.569000' as timestamp(6));
           _col0
----------------------------
 2022-03-30 17:18:09.569000
(1 row)

Presto-Sql : Converting time in string format to date format

Try with the below query it will solve your problem.

Input Query in Presto:

select (hour(date_parse(CheckStartTime,'%T')) + 1) as hr from TableName;

CheckStartTime:

Column name(varchar) of the table in the format of '12:32:20'.

Output:

13 (it will add one hour to the input time)

Presto/SQL - Converting string timestamp to date throws error

As far as explained in the documentation, prestoDB seems to expect timestamps in a format '2001-08-22 03:04:05.321', and dates in a '2001-08-22'.

One solution would be to use a string function to extract the relevant part of the string before converting it. We know that the date part is located before the first space in the string, so.

If you need the date part as a string datatype:

split_part(created, ' ', 1)

If you need the date part as a date datatype:

cast(split_part(created, ' ', 1) as date)

Convert yyyyMMdd string into datetime in Presto

We can try using DATE_PARSE to convert the string input into a date, then take the difference in years to the current date:

SELECT DATE_DIFF('year', DATE_PARSE('20100731', '%Y%m%d'), CURRENT_DATE) AS age
FROM yourTable;