topic Re: today() to insert into date format of Oracle table in SAS Programming

today() to insert into date format of Oracle table

SASAna — Sat, 17 Dec 2016 01:13:27 GMT

Hi SAS users,

A filed "ENTRY_DT" is built as date format in Oracle Database, i am trying to load current date in YYYY-MM-DD format .

I tried to convert it into character with put function & also input function. still getting te below error, where it is inserting the records into table.

ENTRY_DT = put(today(),YYMMDD10.);

ERROR: ENTRY_DT, a numeric column, can only be updated with a numeric expression.

Thanks,

Ana

Re: today() to insert into date format of Oracle table

Tom — Sat, 17 Dec 2016 01:32:10 GMT

Just assign the date value to the variable. What format you use to display a date does not change the value that is stored in the SAS dataset (or ORACLE table).

entry_dt = today()

Re: today() to insert into date format of Oracle table

SASAna — Sat, 17 Dec 2016 02:18:27 GMT

Hi Tom,

Jus loading today() in the entry_dt field is giving resulsts like "1/1/1960 time". I wanted them to be "YYYY- MM-DD" format & also todays date

Re: today() to insert into date format of Oracle table

Tom — Sat, 17 Dec 2016 02:42:39 GMT

Sounds like you have DATETIME field instead of DATE field. What happens if you use DATETIME() function or DHMS(today(),0,0,0) instead?

Re: today() to insert into date format of Oracle table

Patrick — Sat, 17 Dec 2016 04:05:13 GMT

Please post your full code of how you want to load this date value into Oracle.

Be aware that a DATE column in Oracle maps to a SAS DateTime value (and NOT a SAS Date value) and though the value to load should be datetime().

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p06jk0u30uhuj5n18fqw9sxr25lk.htm

If you're interacting with Oracle then make yourself also familiar with:

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p0f64yzzxbsg8un1uwgstc6fivjd.htm

...and with:

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

http://support.sas.com/documentation/cdl/en/engfedsrv/70118/HTML/default/viewer.htm#n167fxh8cc9h2an198cln7b46vb9.htm

Re: today() to insert into date format of Oracle table

SASAna — Mon, 19 Dec 2016 18:48:50 GMT

Hi Tom,

I tried DATETIME() :

data enter;

FORMAT ENTRY_DT DATETIME20.;
ENTRY_DT = DATETIME();

run;

I am getting full date and time being printed in the tables. (12/19/2016 12:39:26 PM) I want it to be YYYY-MM-DD format. Table has Date format for this variable ENTRY_DT.

Thanks,

Ana

Re: today() to insert into date format of Oracle table

Peter_C — Mon, 19 Dec 2016 21:52:51 GMT

Does the wrong presentation come from oracle or SAS ?

Re: today() to insert into date format of Oracle table

Tom — Tue, 20 Dec 2016 00:55:50 GMT

If you want SAS to display only the date part of your datetime values you can use the DTDATE format instead of the DATETIME format.

If you to store only the date part of the current datetime time then store DHMS(TODAY(),0,0,0) instead of DATETIME().