CODEWITHSUNDEEP
sqlstringdatecastingnetezza
pinhead
pinhead

Reputation: 13

casting string to date not working on empty strings in Netezza

I have a string field which is of the format yyyy-mm-dd. I need to convert it to a date field.

This is my SQL query:

select cast(rpad(trim(field_name),10,' ') as date) as field_name
from table_name

It works fine for records that have values, but not for records that are empty strings

I also tried using to_date function, but it gives an error saying "Invalid Date"

Upvotes: 0

Views: 7450

Answers (2)

Allen
Allen

Reputation: 426

Netezza doesn't like to convert an empty string to a date - unlike SQL Server - so it's good to use a case statement to convert the empty string to a null. Try this:

select case when field_name = '' then NULL else date(field_name) end as field_name
from table_name;

Upvotes: 1

Teja
Teja

Reputation: 13534

Check the below query.

SELECT TRIM(LEADING 0 FROM CAST("YourStringColumn" AS DATE))

Upvotes: 1

Related Questions