Remove the timestamp from the datetime field and update the timestamp with 00:00:00 in Pass thru SQL

[Anju]

HI All, I am trying to update the format for only the timestamp and make it 00:00:00 without changing the date, Please can you suggest some formats I can try in the Pass through update I posted below

Have - 

data have;
input bus_date datetime25.;
format bus_date datetime25.;
datalines;
25DEC2020:16:12:18
.
;
run;

Want - 

bus_date 25DEC2020:00:00:00

proc sql;
connect to oracle as myconn (user=smith password=secret
path='myoracleserver');

execute
(
update have
set
bus_date=???
where
STATUS = '0'
) by ORACLE;

disconnect from myconn;
quit;

[Reeza]

This is an Oracle question not SAS question since you're using SQL pass through the 
SQL needs to be Oracle compliant.

Try the TRUNC() function.

bus_date=trunc(bus_date)

---

@Anju wrote:

HI All, I am trying to update the format for only the timestamp and make it 00:00:00 without changing the date, Please can you suggest some formats I can try in the Pass through update I posted below

Have - 

data have;
input bus_date datetime25.;
format bus_date datetime25.;
datalines;
25DEC2020:16:12:18
.
;
run;

Want - 

bus_date 25DEC2020:00:00:00

proc sql;
connect to oracle as myconn (user=smith password=secret
path='myoracleserver');

execute
(
update have
set
bus_date=???
where
STATUS = '0'
) by ORACLE;

disconnect from myconn;
quit;

---