Presto SQL - Converting a date string to date format

Question

I m on presto and have a date formatted as varchar that looks like -   7 14 2015 8 22 39 AM   I ve looked the presto docs and tried various things cast  date format  using split part to parse and then cast  and am not getting this to convert to a date format that I can use with functions like date diff   I ve tried   cast fieldname as timestamp  date format fieldname    Y- m- d  T    Both give me an error like this    Value cannot be cast to timestamp  3 31 2016 6 05 04 PM    How do I convert this

User · Answer

select date format date parse t payDate   Y- m- d  H  i  S     Y- m- d   as payDate      from testTable  t      where t paydate is not null and t paydate  lt  gt

User · Answer

SQL 2003 standard defines the format as follows    lt unquoted timestamp string gt       lt unquoted date string gt   lt space gt   lt unquoted time string gt   lt date value gt       lt years value gt   lt minus sign gt   lt months value gt   lt minus sign gt   lt days value gt   lt time value gt       lt hours value gt   lt colon gt   lt minutes value gt   lt colon gt   lt seconds value gt    There are some definitions in between that just link back to these  but in short YYYY-MM-DD HH MM SS with optional  mmm milliseconds is required to work on all SQL databases

User · Answer

Converted DateID having date in Int format to date format  Presto Query  Select CAST date format date parse cast dateid as varchar 10      Y m d      Y  m- d   AS DATE  from      Table Name limit 10

User · Answer

I figured it out  The below works in converting it to a 24 hr date format   select date parse  7 22 2016 6 05 04 PM    m  d  Y  h  i  s  p     See date parse documentation in Presto

User · Answer

You can also do something like this  date cast  2016-03-22 15 19 34 0  as timestamp

User · Answer

If your string is in ISO 8601 format  you can also use from iso8601 timestamp

User · Answer

Use  cast date parse inv date created   Y- m- d  h24  i  s   as date   Input  String timestamp  Output  date format  yyyy-mm-dd

User · Answer

date format requires first argument as timestamp so not the best way to convert a string  Use date parse instead  Also  use  c for non zero-padded month   e for non zero-padded day of the month and  Y for four digit year  SELECT date parse  7 22 2016 6 05 04 PM     c  e  Y  r

[sql] Presto SQL - Converting a date string to date format

Examples related to sql

Examples related to presto