EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.

SAS Date or Datetime format Tutorial
How to Convert integers to Date/datetime in SAS/Netezza?

Example: say, you have some large integer numbers, which is the numeric expression for some date(num of days since 1960-01-01) or for some datetime
(num of seconds since 1960-01-01:00:00:00). Now you want to transfer them into the actual date or datetime. How do we get this in SAS? What about in Netezza?

/******************************************************/
data test1 ;
date=19529;
date_integer=19529;
time=1687361425;
time_seconds=1687361425;
format date date9. time datetime19.; run;

proc sql; create table nz_work.test1
( distribute_on = 'random'
bulkload=yes bl_options=' logdir "Directory\netezza_log" ' )
as select * from work.test1; quit;

proc sql;   connect to netezza (server='server_name' database=database1
execute (create table test2 as select *, cast(date('01-01-1960')
+ interval '1 day' * date_integer as date) as date_integer1,

date('01-01-1960') + interval '1 sec' * time_seconds as datetime1,
/* In netezza, the days or seconds count starting from 1970-01-01, not 1960 */
date 'epoch' + interval '1 sec' * time_seconds as datetime2,
date '01-01-1970' + interval '1 sec' * time_seconds as datetime3
from test1 as a
distribute on random
;) by netezza;
quit;

Some other useful datetime related functions:
--first of this year
select date_trunc('year', CURRENT_DATE)

--how to get first of the month given a date
select date_trunc('month', current_timestamp)

--how to display monthname-YYYY format - ie December-2012
select trim(to_char(now(),'Month')) || '-' || year(current_date);

-how to display YYYY-MM format - ie 2012-12

select TO_CHAR(current_date,'YYYY-MM');

--truncate timestamp given a date
select date(current_timestamp)--this does not require SQL Extension Toolkit
or
select date_trunc('day', current_timestamp) --requires SQL Extension Toolkit

--select next Monday's date based on today's date
select next_day(DATE(current_timestamp), 'Monday') --if your date is a timestamp and you just want the truncated date for next monday
select next_day(current_date, 'Monday')--if your date is a truncated date

--subtract 1 month from today's date
select date(current_date -  cast('1 month' as interval));

--add 6 days  from today's date
select date(current_date +  cast('6 days' as interval));

--Difference in days between 2 dates (if you have sql exension toolkit installed)
select EXTRACT (DAY FROM DateCol1 -DateCol2) as difference_in_days
from tableA
limit 100;
OR
-- epoch returns number of seconds
select  EXTRACT(epoch FROM DateCol1 -DateCol2)/86400 as difference_in_days
from TableA
limit 100;

--Difference in hours between 2 dates (epoch returns number of seconds)
select  EXTRACT(epoch FROM DateCol1 -DateCol2)/3600 as difference_in_hours
from TableA
limit 100;

--for those of you who are used to SQL SERVER's datepart function, you are in luck!
select date_part('day', current_date) as day_of_month, date_part('week', current_date) as week_of_year

--Add 1 hour to a date

select TO_CHAR(datetime(date('2013-02-26') +  cast('1 hour' as interval)),'YYYY-MM-DD HH24:MI:SS')