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

Reply
Occasional Contributor
Posts: 13

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

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:MMSmiley FrustratedS. 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

Super User
Posts: 17,912

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: 13

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: 17,912

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: 17,912

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: 13

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

Thanks Reeza.

Respected Advisor
Posts: 3,900

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.

Occasional Contributor
Posts: 13

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: 17,912

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

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: 13

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

Thank you.

 

John

Ask a Question
Discussion stats
  • 11 replies
  • 3493 views
  • 0 likes
  • 5 in conversation