can this be done? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

can this be done?

Author

Topic

helpme
Posting Yak Master

141 Posts

Posted - 2005-02-05 : 14:01:32

I have an instance of sql server with a linked server to an oracle database. I am having problems copying rows over into an sql server table from oracle because the oracle table has a date column that has some dates that are default fake dates (prior to 01-01-1753). Is there a way in t-sql to check for and convert the fake dates to something else, but keep the real dates intact (ie. convert
01-01-1700 to 01-01-1753, but keep 02-01-2005 as 02-01-2005)?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-05 : 15:09:40

You could use the ISDATE function to check for a valid sql server datetime.
Also use the ISO date format yyyymmdd to avoid confuxion about months/days

DECLARE @d CHAR(10)
SET @d = '17000101'
SELECT CAST(CASE WHEN ISDATE(@d) = 1 THEN @d ELSE '17530101' END AS DATETIME)
SET @d = '20050201'
SELECT CAST(CASE WHEN ISDATE(@d) = 1 THEN @d ELSE '17530101' END AS DATETIME)

-------------------------------------------- 
1753-01-01 00:00:00.000
                                          
-------------------------------------------- 
2005-02-01 00:00:00.000


SET DATEFORMAT mdy
SELECT CAST('02-01-2005' AS DATETIME)

-------------------------------------------- 
2005-02-01 00:00:00.000

SET DATEFORMAT dmy
SELECT CAST('02-01-2005' AS DATETIME)

-------------------------------------------- 
2005-01-02 00:00:00.000

rockmoose

helpme
Posting Yak Master

141 Posts

Posted - 2005-02-05 : 15:46:04

Is there a way to check this with a select against an oracle table?

I tried something like this:

SELECT ISDATE(DATE_FIELD) as DATE_FIELD FROM ORACLE_LINK..ORACLEUSER.TABLE_NAME

I'm getting the 'error converting data type dbtype_dbtimestamp to datetime' error.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-05 : 16:06:25

Even if ISDATE() didn't fail, it would be very slow to work in this query. You'd want to convert the date on the Oracle side before it even got to SQL Server, otherwise it would fail. I don't think you'll be able to do this without using OPENQUERY. Something like this should work:

SELECT * FROM OPENQUERY(ORACLE_LINK, 'SELECT CASE WHEN EXTRACT(YEAR FROM DATEFIELD)<1753 THEN TO_DATE(''17530101'',''YYYYMMDD'') ELSE DATE_FIELD END DATE_FIELD, * FROM ORACLEUSER.TABLE_NAME')

Note that this is off the top of my head and I'm not an Oracle expert, so you will probably have to refine the query. If it DOES work right off the bat, you owe me a

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-05 : 16:08:03

You could try something like this:

SELECT ISDATE(DATE_FIELD) FROM
(SELECT DATE_FIELD FROM ORACLE_LINK..ORACLEUSER.TABLE_NAME) oratbl

SELECT ISDATE(DATE_FIELD) FROM
OPENQUERY(ORACLE_LINK,'SELECT DATE_FIELD FROM ORACLEUSER.TABLE_NAME')

Another option is to transfer the dbtype_dbtimestamp field
as character data into sql server and perform the ISDATE on the char type.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources