0

Please help in converting the date field to integer in YYYYMMDD format Date col- YYYY-MM-DD HH:MM:SS - 2020-05-20 00:00:00 Required output - YYYYMMDD - 20200520 IN Aginity workbench (Netezza).

Mihul12
  • 1
  • 1

2 Answers2

2

Reference: https://www.ibm.com/docs/en/psfa/7.2.1?topic=extensions-conversion-functions

Get the date as string

SYSTEM.ADMIN(ADMIN)=> select to_char(to_date('2021-01-01 10:11:12','YYYY-MM-DD HH:MI:SS'), 'YYYYMMDD') as str;
   STR
----------
 20210101
(1 row)

Now convert from string to integer base 10

SYSTEM.ADMIN(ADMIN)=> select string_to_int(to_char(to_date('2021-01-01 10:11:12','YYYY-MM-DD HH:MI:SS'), 'YYYYMMDD'), 10) as num;
   NUM
----------
 20210101
(1 row)

To verify it is indeed an integer let us add 5 to it

SYSTEM.ADMIN(ADMIN)=> select string_to_int(to_char(to_date('2021-01-01 10:11:12','YYYY-MM-DD HH:MI:SS'), 'YYYYMMDD'), 10) + 5 as numplus5;
 NUMPLUS5
----------
 20210106
(1 row)

Replace the date time string with your column in above examples.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Amalendu Haldar
  • 31
  • 1
1

to_char would be helpful here.

select string_to_int(
    to_char(date_column, ‘YYYYMMDD’), 
     10 -- the base 
 )
...
Aniket Kulkarni
  • 471
  • 2
  • 2