0

I've got a temporary work table with a date variable source_datetime in SAS DIS. This variable is in the DATETIME22.6 format.

I have a teradata table with a date field target_date (type DATE), and using a table loader I am attempting to map source_datetime to target_date. When I run the transformation I get the error

ERROR: A SAS value cannot be converted to a Teradata date

The temporary work table is populated with good data. When I attempt the conversion from DATETIME22.6 to DATE9. the output looks like "*********".

Much gratitude.

Rookatu
  • 1,487
  • 3
  • 21
  • 50
  • Can you narrow it to a particular row/value that is causing the error? – Robert Penridge May 01 '15 at 14:38
  • All rows have the same date value. It occurs when trying to load the first row – Rookatu May 01 '15 at 14:57
  • Oh sorry you did say that. =) What type is your date column? – Robert Penridge May 01 '15 at 15:10
  • Do you have the SAS Formats package installed on your Teradata system? – Rob Paller May 01 '15 at 19:15
  • What underlying value is in that field? When you say you convert to DATE9 from DATETIME, what are you doing to convert it?? – Joe May 01 '15 at 19:43
  • Hey Joe. I was using the `DHMS` function to do the conversion. I have solved my error, though I did so in a roundabout way which sheds no light on what the issue was. Essentially, there were conversions from `DATE9.` to `DATETIME26.6` happening back and forth, and at some point SAS DIS stopped displaying the date. I changed the logic of the job, but what is the protocall here vis a vis an answer to the post? Do I delete the question? Thx – Rookatu May 04 '15 at 17:43

1 Answers1

2

I know very little about either DIS or Teradata, but I don't think either are related to your problem.

Datetime values are the number of seconds since Jan 1, 1960 00:00:00. Date values are the number of days since Jan 1, 1960.

It sounds like you are trying to apply the date9 format to a datetime value. If you do this, it will usually look like ********* because the number of seconds is way too high to be represented as a date. If you want to keep the datetime value but have it formatted like a date, use the dtdate9 format. Otherwise, you could convert the datetime value to a date value with the datepart() function and then use the date9 format.

DWal
  • 2,752
  • 10
  • 19
  • Hi DWal. Yeah, I had been using `DATEPART` to go from `datetime` to `date9.`, and used `dhms` to go the other way around. Applying either once worked fine. But the job for some reason had been set up to do these conversions multiple times, and I think this is what messed up the program. I cannot explain in any more detail than that, and it is just a suspicion, but as you might expect there's a better way to code the job than that and I've since implemented the better approach. Thanks for the help on this! – Rookatu May 04 '15 at 18:28