How to convert the datetime from oracle when coding SAS?

[Jacey]

Hello!

When I run the code as below, I will get a table containing three variables. No1 is char_datetime, a character variable, No2 is date_datetime, a date variable, and No3 date is also a date variable. But I have to used oracle function, such as to_number, or to_date in order to get a date format like 03/31/2018 or Mar 31, 2018. Do u know how to apply? Also, do u know how to convert the variable Char_datetime from character into number with to_number function using the information below? Thanks!

proc sql;

CONNECT TO teradata as database (user= password= tdpid= mode=);

create table want as select * from connection to database

 (Select   char_datetime, date_datetime,date );quit;

char_datetime	date_datetime	date
1253580	25FEB2018:00:00:00.000	11MAR2018
1215410	25FEB2018:00:00:00.000	14MAR2019
1811190	25FEB2018:00:00:00.000	19MAR2020
1717030	18MAR2018:00:00:00.000	21MAR2021
1807470	18FEB2018:00:00:00.000	31MAR2022
2117550	25FEB2018:00:00:00.000	11MAR2023
1214230	25FEB2018:00:00:00.000	11MAR2024
1207320	25FEB2018:00:00:00.000	11MAR2025

[SuryaKiran]

Did you try Implicit way (Libname) instead of pass-through. Do you have a reason to stick with pass-through?

Note: Your are connecting to teradata so why do you need Oracle functions.

LIBNAME ter teradata  user= password= ;

proc sql;
create table want as 
select INPUT(char_datetime,$12.) as num_datetime,
		DATEPART(date_datetime) format=date9. as date_datetime,
		date format=date9. 
	from ter.table;
quit;