DATA Step, Macro, Functions and more

today() to insert into date format of Oracle table

Reply
Frequent Contributor
Posts: 95

today() to insert into date format of Oracle table

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

 

 

Super User
Super User
Posts: 7,060

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

[ Edited ]

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()

 

Frequent Contributor
Posts: 95

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

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

Super User
Super User
Posts: 7,060

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

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

 

 

Respected Advisor
Posts: 4,173

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

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#p06jk0u30uhuj5n18f...

 

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#p0f64yzzxbsg8un1uw...

 

...and with:

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

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

 

Frequent Contributor
Posts: 95

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

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

Super User
Super User
Posts: 7,060

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

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().

Valued Guide
Posts: 2,177

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

Does the wrong presentation come from oracle or SAS ?
Ask a Question
Discussion stats
  • 7 replies
  • 477 views
  • 0 likes
  • 4 in conversation