Netezza SQL syntax to convert numeric YYYYMMDD format into date

Question

We have a Netezza table that contains dates stored in a numeric YYYYMMDD format (eg 20090731).

What is the best Netezza syntax to use to convert this into date format?

eg

SELECT somefunction(20090731) as NZDATE

?

Answer 1

aah - my efforts were thwarted originally due to invalid dates. The below DOES work as long as you wrap it in a case when statement to catch the bad ones!

select  to_date(substring(20090731 from 1 for 8),'YYYYMMDD') as NZDATE

(obviously the 20090731 should be replaced with the name of the numeric variable..)

Answer 2

very nice .. its working for me .. Thanks, Arun M

Answer 3

Hi,

This is new syntax for SUBSTRING function. working well. Thanks a lot. I'm expecting more related syntax of Netezza .

Thanks, Sudhagar V

Answer 4

Hi,

U can use this one as a best one.

SELECT TO_DATE('20090731','YYYYMMDD') as NZDATE

Thanks, Sudhagar.