BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
alepage
Barite | Level 11

Hello, I would like to convert a datetime and take the datepart in snowflake. The datepart(AUTHRSTN_DT) is not working.

How to do that?

 

 

AUTHRSTN_DT
05JUN2023:08:24:37.000000

Please note that AUTHRSTN_DT format is datetime25.6

authorisation_date=datepart(AUTHRSTN_DT);

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

If it's coming over as a legit datetime value into SAS it should work. However, if it's coming over as character then you'll have to convert it.

 

data g;
 AUTHRSTN_DT = '05JUN2023:08:24:37.000000';
 AUTHRSTN_DT_N = input(AUTHRSTN_DT ,datetime25.6);
 justDate = datepart(AUTHRSTN_DT_N );
 format justDate date9.;
run;

 

If using SAS/ACCESS to Snowflake libname, then you might need to use the DBSASTYPE option on the libname statement to ensure the column is treated as a SAS datetime.

Register for SAS Innovate 2025!! The premier event for SAS users, May 6-9 in Orlando FL. Sign up now for the best deals!

View solution in original post

6 REPLIES 6
ChrisHemedinger
Community Manager

If it's coming over as a legit datetime value into SAS it should work. However, if it's coming over as character then you'll have to convert it.

 

data g;
 AUTHRSTN_DT = '05JUN2023:08:24:37.000000';
 AUTHRSTN_DT_N = input(AUTHRSTN_DT ,datetime25.6);
 justDate = datepart(AUTHRSTN_DT_N );
 format justDate date9.;
run;

 

If using SAS/ACCESS to Snowflake libname, then you might need to use the DBSASTYPE option on the libname statement to ensure the column is treated as a SAS datetime.

Register for SAS Innovate 2025!! The premier event for SAS users, May 6-9 in Orlando FL. Sign up now for the best deals!
Patrick
Opal | Level 21

What's the value of &accountingDate?

 

What happens if you run below? Do you get any eodreport_date with a select_flg=0 that you would have wanted to select?

data test;
  set inforce_auto_cntrctver_org;
  if eodreport_date <= &accountingdate. then select_flg=1;
  else select_flg=0;
run;

proc freq data=test;
  table eodreport_date*select_flg /nocol norow nopercent;;
run;

 

AhmedAl_Attar
Ammonite | Level 13

Hi @alepage 

if you are trying to pass this value over to Snowflake and use it there, then you may want to look at this SAS/ACCESS Interface to Snowflake

https://documentation.sas.com/doc/en/pgmsascdc/v_046/acreldb/n1d5j8d7wegfezn1irjj3hcrne1n.htm

 

Hope this helps

Patrick
Opal | Level 21

@alepage wrote:

Hello, I would like to convert a datetime and take the datepart in snowflake. The datepart(AUTHRSTN_DT) is not working.

How to do that?

To do what? To load a SAS table into Snowflake or to use it in a where clause to query Snowflake?

If query: If you run Proc Contents against the Snowflake table what type and format does the column show?

 

What's not working with datepart()? You get an error, or an undesired result, or the query just runs forever?

 

If it's for a query formulated in SAS SQL, the column in Snowflake is of type DateTime and you want all rows matching a specific date then below query should work and also get pushed to the DB for execution

where '05JUN2023:00:00:00'dt <= AUTHRSTN_DT < '06JUN2023:00:00:00'dt

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 2462 views
  • 1 like
  • 5 in conversation