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

Anju — Tue, 09 Aug 2022 16:23:40 GMT

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;

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

Reeza — Tue, 09 Aug 2022 19:51:50 GMT

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;

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

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

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