EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer

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 ;
        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
        user=myuser pwd=mypassword connection=global);
        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,
convert numerical integers to date or datetime
  /* 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;

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
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;
-- 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')

Related links:
Continue to: How do I obtain percentiles not automatically calculated?   SAS tutorial home
Back to Why 0.1 + 0.1 is not equal to 0.2   Statistics Tutorial Data Mining