DATA Step, Macro, Functions and more

Oracle Insert sysdate

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Oracle Insert sysdate

Hello,

 

i want to insert SYSDATE into an oracle table.

 

This works:

 

		proc sql noprint threads;
		connect to ORACLE as oracle ( user=XX password=XX path=XX );

		INSERT INTO oraCon.TABLENAME
		(
			LAST_UPDATE
		) 
		VALUES
		(
			'01JAN2016:00:00:00'DT
		);
		disconnect from oracle;
		quit;

 

 

 

But how can i insert sysdate/datetime (the actual date and time) instead of the constant "'01JAN2016:00:00:00'DT"?

 

Thank you very much.

 

Best regards

George


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 7,401

Re: Oracle Insert sysdate

Maybe (can't check it):

		VALUES
		(
			"%sysfunc(date(),date9.):%sysfunc(time(),tod8.)"dt
		);

View solution in original post


All Replies
Super User
Posts: 17,829

Re: Oracle Insert sysdate

Use the DATETIME() function. 

 

*Create a macro variable;
%let myDate = %sysfunc(datetime(), datetime21.);

*Display macro variable for testing;
%put &myDate;
Solution
3 weeks ago
Super User
Super User
Posts: 7,401

Re: Oracle Insert sysdate

Maybe (can't check it):

		VALUES
		(
			"%sysfunc(date(),date9.):%sysfunc(time(),tod8.)"dt
		);
Occasional Contributor
Posts: 10

Re: Oracle Insert sysdate

Thanks you very much

 

Best regards

George

Super User
Super User
Posts: 6,500

Re: Oracle Insert sysdate

The automatic macro variables SYSDATE9 and SYSTIME are set to the date and time when your SAS session started. If you want to use those then change use something like:

proc sql noprint threads;
connect to ORACLE as oracle ( user=XX password=XX path=XX );
INSERT INTO oraCon.TABLENAME (LAST_UPDATE)
VALUES ("&sysdate9:&systime"dt)
;
disconnect from oracle;
quit;

If instead you want to use the current date and time then you can call the DATETIME() function instead.  The VALUES statement does not take functions so wrap the function call in the %SYSFUNC() macro function.

VALUES (%sysfunc(datetime()))

 

Occasional Contributor
Posts: 10

Re: Oracle Insert sysdate

Thank you very much - works also correctSmiley Happy
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 119 views
  • 4 likes
  • 4 in conversation