hp746
asked on
Oracle Linked server date format issue from sql server 2008
I am trying to get date from oracle using linked server but the date format it is getting is like this
0009-01-10 for 2009-01-10. does any one seens this issue before,
0009-01-10 for 2009-01-10. does any one seens this issue before,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There is no issue. What format do you want the date in?
ASKER
For 2009 its showing 0009 and for 2010 its showing 0010.
Then your data is incorrect. 0009 and 0010 are valid years in an Oracle database. If you insert a 2 digit year into a date field with a 4 digit year as a date mask, you will get that.
If you connect directly to the Oracle database using SQL*Plus, what do you see as a year for those records? My guess is you will see 0009 and 0010.
I believe that if you change the date format to use RRRR rather than YYYY it should correct that.
If you connect directly to the Oracle database using SQL*Plus, what do you see as a year for those records? My guess is you will see 0009 and 0010.
I believe that if you change the date format to use RRRR rather than YYYY it should correct that.
ASKER
Date is oracle is of this format 30-SEP-09 checked thru SQL plus.
I tried to convert using RRRR as well but it is still returning year as 0009 and 0010.
This issue i am getting in staging server but in dev its returning 2009 and 2010 for year for same table structure and using same query.
Not sure whats going wrong here. any help will be appreaciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
we have inhouse solution
ASKER
This data i am pulling from SQL server 2008 using oracle linked server . here is the query.
SELECT DISTINCT date_effective FROM test_link..test_schema.tes
its returning dates as 0009-11-21 00:00:00.0000000. what could be the issue here.
Thanks