CODEWITHSUNDEEP
sqlsas
Neoleogeo
Neoleogeo

Reputation: 323

change all values in a date column to the end of the month

I have a column 'timestamp' in format Datetime. To merge with another table it would be very convenient to create a column that transforms:

 06AUG2015:21:42:44

Into

 31AUG2015:00:00:00

Any ideas how to go about this?

Upvotes: 2

Views: 300

Answers (2)

Richard
Richard

Reputation: 27516

You can join on the intnx without having to create a new column.

Example, presuming transaction table has column timestamp that contains SAS datetime values, and monthEnd table has column dayStamp that contains SAS date time values restricted to the start of the last day of a month.

select 
  … 
from 
  transaction as WithinMonth
join 
  monthEnd
on
  intnx('dtMonth', WithinMonth.timestamp, 1, 'B') - 86400
  /* computed: start of last day of month that contains timestamp */
  = 
  monthEnd.dayStamp

Upvotes: 0

Sanek Zhitnik
Sanek Zhitnik

Reputation: 726

You can run intnx function. Example:

data dates;
format d1 d2  DATETIME20. d3 date9. d4 DATETIME20.;
d1 = '06AUG2015:21:42:44'dt;
/*E as last parameter mean END*/
d2 = intnx('dtMonth', d1, 0,"E");
d3=datepart(d2);
d4 = dhms(d3,0,0,0);
run;

d1 -- initial date

d2 -- the last date of month

d3 -- date part of last date of month

d4 -- date with dropped HMS to zeros

Result

+--------------------+--------------------+-----------+--------------------+
|         d1         |         d2         |    d3     |         d4         |
+--------------------+--------------------+-----------+--------------------+
| 06AUG2015:21:42:44 | 31AUG2015:23:59:59 | 31AUG2015 | 31AUG2015:00:00:00 |
+--------------------+--------------------+-----------+--------------------+

P.S. thank you @Tom

data dates;
format d1 d4 DATETIME20.;
d1 = '06AUG2015:21:42:44'dt;
d4 = intnx('dtday',intnx('dtMonth', d1, 0,'E'),0,'B');
run;

Upvotes: 2

Related Questions