?
Solved

Oracle Linked server date format issue from sql server 2008

Posted on 2010-09-24
8
Medium Priority
?
827 Views
Last Modified: 2013-12-18
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,
0
Comment
Question by:hp746
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 2000 total points
ID: 33755959
The format of the string that Oracle returns for a date is controlled by settings which you can change.

To change at the session level, you can use:

ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY';

or any other variant of a date format (this is just an example).

It can also be changed at the database level by changing the same parameter.

There is also a registry setting that you can set to change the default format.

All of these will set defaults.  They can always be overridden at the query level with a TO_CHAR(<date_field>, 'MM/DD/YYYY'), again, any valid format is usable here.
0
 

Author Comment

by:hp746
ID: 33756079
Hi John,

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.test_m_view

its returning dates as  0009-11-21 00:00:00.0000000.  what could be the issue here.

Thanks
0
 
LVL 35

Expert Comment

by:johnsone
ID: 33756300
There is no issue.  What format do you want the date in?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:hp746
ID: 33757352
For 2009  its showing 0009  and for 2010  its showing 0010.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 33757687
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.
0
 

Author Comment

by:hp746
ID: 33758287

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.
0
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 33765451
When you are checking the date in SQL*Plus, you are only looking at the 2 digit year.  You need to check the 4 digit year.  Use TO_CHAR(<date_field>, 'DD-MON-YYYY HH24:MI:SS').  That will show you the whole date.  This should confirm what the full 4 digit year is on the Oracle side.

RRRR was a long shot, I wasn't sure that would work.  I don't have an Oracle database that I can try this on right now, but this conversion may work.

TO_DATE(TO_CHAR(<date_field>, 'MM/DD/YY HH24:MI:SS'), 'MM/DD/RR HH24:MI:SS')

What this should do is convert it to a string as a 2 digit year an then convert it back to a date using the RR format.  That should fix the year for you.  If that does not work, you are going to have to go through string manipulations to fix the dates.
0
 

Author Closing Comment

by:hp746
ID: 33833176
we have inhouse solution
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question