1

In SQL server, there is TRY_CAST method available to check if cast is possible or not. If cast is not possible then it will set NULL as value.

Is there any similar method available in presto? Or How to achieve similar behavior in presto?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Hardik
  • 31
  • 1
  • 4
  • What are you trying to cast here, and can you include sample data? – Tim Biegeleisen Apr 26 '21 at 06:02
  • I want to parse date from varchar. Sample data: "4/11/2019 12:29:49 PM" If date is not correct then it should set NULL. – Hardik Apr 26 '21 at 06:08
  • Not sure if Presto has a try cast, but if it doesn't, then you're basically out of luck. Instead, you'll have to know beforehand what the format of your date strings are. – Tim Biegeleisen Apr 26 '21 at 06:12

2 Answers2

2

That function has existed for many years. You can find it in the docs.

SELECT try_cast('abc' AS bigint)

=>

 _col0
-------
  NULL
(1 row)
Martin Traverso
  • 4,731
  • 15
  • 24
0

I tried to use TRY_CAST to convert a string into a timestamp and compare it with my own converting code.

select 
CASE
 WHEN LENGTH(received)<3 
   THEN NULL
 ELSE DATE(DATE_PARSE(substr(received, 1,10), '%m/%d/%Y'))  
 END AS "RECEIVED_CV"
,DATE(TRY_CAST(received AS TIMESTAMP)) AS RECEIVED
from (table)

The result is very interesting. My code will produce the date but the TRY_CAST will produce null. The original string value is like this "02/05/2021 15:45:57.000000"

Comparison of the results

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Chris Hang
  • 1
  • 1