SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Timestamp format of YYYY-MM-DD HH:MM:SS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Timestamp format of YYYY-MM-DD HH:MM:SS

[ Edited ]

Hi,

 

We are using an Extract Transformation in SAS DI to extract data from Teradata to a file. One of the columns is a timestamp and in Teradata the format is

 

YYYY-MM-DD HH:MM:SS

. The default SAS timestamp format returns the value in format like 30MAY2016:21:32:32 or yyyy-mm-ddThh:mm:ss with a ‘T’ between the Date and Hour. Couple of options that we have tried are

 

1) Put in Proc format step in the precode of the job as below and use the format in the DI user written code.

 

proc format;
  picture ymdhms other='%0Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime);
run;

 

2) Cast the value to character data type to display it in the same format as the database?

 

Is there any existing SAS Date format that could be used to display the timestamp in the same format as the Teradata Timestamp format i.e, 'YYYY-MM-DD HH-MM-SS'.

 

John


Accepted Solutions
Solution
‎08-30-2017 12:39 PM
Respected Advisor
Posts: 4,239

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

[ Edited ]

Actually: The Timestamp datatype could also contain fractional seconds. If this is the case and given the usual precision challenges when transfering fractional values between platforms, I'd go for an approach where you cast the timestamp on the Teradata side to a string and then load the string to SAS.


Editor's note: another reader had a similar question and used CAST in PROC SQL passthru code to Teradata to reconcile date comparisons.

 

See also the complete reference on ISO datetime formats available in SAS.

 

And as @arpitagarwal512 shared, this picture format can produce the original requested output:

 

proc format;
picture dtpic
other='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime)
;
run;
data a;
 a = datetime();
 format a dtpic.;
run;

View solution in original post


All Replies
Super User
Posts: 20,731

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Your question has HH:MMSmiley FrustratedS but the last portion of your question has HH-MM-SS which one are you looking for?

Typically, it's HH:MMSmiley FrustratedS.

 

I don't think there's a standard one in the format you'd like. There is likely 2 together, one for the date portion and one for the time portion that would work, but not sure about a datetime format. 

 

You can review all the formats here, and they're organized by category to help you filter the date/datetime formats. The IS 8601 section is also date & time formats. 

 

https://support.sas.com/documentation/cdl/en/leforinforref/64790/HTML/default/viewer.htm#n0p2fmevfgj...

Occasional Contributor
Posts: 16

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Hi Rezza,

 

I am looking for the output format YYYY-MM-DD HH:MMSmiley FrustratedS. In the link the closest one is

 

$N8601EXw.dFormat

 

But it has a 'T' between the Date and Time part.

 

 

Thanks

John

Super User
Posts: 20,731

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Then the options you've outlined are the best alternatives, either creating your own or using a combination of date & time formats and converting to character.

 

I'm not sure there's anything else we can suggest. If it doesn't exist, it doesn't exist.  

Super User
Posts: 20,731

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

You can add a suggestion to SAS Idea's for it to be added in a future release. 

Occasional Contributor
Posts: 16

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Thanks Reeza.

Solution
‎08-30-2017 12:39 PM
Respected Advisor
Posts: 4,239

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

[ Edited ]

Actually: The Timestamp datatype could also contain fractional seconds. If this is the case and given the usual precision challenges when transfering fractional values between platforms, I'd go for an approach where you cast the timestamp on the Teradata side to a string and then load the string to SAS.


Editor's note: another reader had a similar question and used CAST in PROC SQL passthru code to Teradata to reconcile date comparisons.

 

See also the complete reference on ISO datetime formats available in SAS.

 

And as @arpitagarwal512 shared, this picture format can produce the original requested output:

 

proc format;
picture dtpic
other='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime)
;
run;
data a;
 a = datetime();
 format a dtpic.;
run;
Occasional Contributor
Posts: 16

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Thanks Patrick. Suggested the same to development team.

Occasional Contributor
Posts: 7

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Hi Team,

 

This below proc format will not work when you have date like "12/11/1969"(dd/mm/yyyy) so this will generate putput like "1969-12-11 00:00:00" and for date "13/11/1969" it will generate out " 1969-11-13 00:00:00"

proc format;
picture dtpic
other='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime)
;
run;

 here for inout "12/11/1969"(dd/mm/yyyy it should generate output  "1969-11-12 00:00:00"

Super User
Posts: 20,731

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Posted in reply to arpitagarwal512

The variable you're showing is a date but the format is datetime format. 

 

It looks like you should first create a datetime variable using DHMS() function. 

 

New_var = DHMS(old_var, 0, 0, 0);

format new_var dtpic.;

Occasional Contributor
Posts: 13

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Sorry if this is too late to the party, but the format you're looking for is DATETIME20.9

If you need an  AM/PM in the date you can use DATEAMPM. (DATEAMPM13. for 13 characters, ect.)

The one thing I haven't been able to put together is how to get it to show it as DDMMYYYY. Seems to only do DDMMMYY (With the MMM being text).

Cheers

Monster

Occasional Contributor
Posts: 16

Re: Timestamp format of YYYY-MM-DD HH:MM:SS

Posted in reply to XMonsterX

Thank you.

 

John

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 8066 views
  • 1 like
  • 5 in conversation