06-20-2013 05:51 PM
I'm not getting any matches when I try to join 2 tables together on a date field when using SAS EG 4.3.
One table was created in an Oracle DB & the other in Teradata SQL Assistant 13.11.
Also, the Oracle date field is in a Date/Time format ('DDMONYYYY:HH:MMS') & the Teradata date field is in date format ('DDMONYYYY')
I've tried changing the format of the Oracle date/time format to just date 'DDMONYYYY' in order to match the Teradata table but I still get no matches.
I've checked both tables & there are matching values in both in the 'DDMONYYYY' format.
Can anyone advise what i should try to get these values to match?
06-20-2013 06:54 PM
Ok, the date variable straight from Oracle will be taken as date time format by SAS. So you need to truncate it into date before you can join with other date. if you are writing BASE code or using a Store Process in EG, then you can apply datepart() function to your Oracle date variable in your code before the join. However, You could be using Query builder, in that case, you need to "add" a computed variable using datepart(), then use this new variable to do the join.
06-20-2013 07:43 PM
You're right, I am using the query builder & I've used the datepart function as datepart(Fieldname) which I tested on one value that I know matches.
Only thing is that it has changed the date to 19521 which I understand to be the number of days since 1st Jan1960. This isn't going to match a date format in my other table though ??
What do I do from here?
06-20-2013 07:48 PM
If its a date it may, if it's a char you can do a similar conversion, using datepart, or input/put to create a sas date and join.
Basically you need to make the two dates look the same, so you create two new variables that have the same type. I'm not familiar with Teradata so I can't explain how to do it for that type of data.
06-20-2013 08:02 PM
I've already tried changing the values to match as I stated at the start.
"I've tried changing the format of the Oracle date/time format to just date 'DDMONYYYY' in order to match the Teradata table but I still get no matches."
I tried applying a Format to both fields such as 'DDMMYYN8.' but still no matches even though the values look identical on the tables??
06-20-2013 10:51 PM
After a lot of testing the functions, I used DATDIF to manually subtract the date field value from 1st Jan 1960 which gave me a number, I could then match these number fields.
I think that's what you were trying to tell me Reeza.
This is a lot of messing about to sort such a simple issue out???
I thought I'd just have to truncate the HH:MMS off the date Time field & I'd get matches, but no ...
I used datepart to change the date from a normal date format to a number on the date/time field & then create a number from a date using datedif for the other table.
DATDIF('01jan60'd, my_date_field ,'act/act').
Thanks to both of you - surely there's an easier way ???
Need further help from the community? Please ask a new question.