0

From SQL Server , I need to be able to pull some information from an Oracle database based on a date column. 

SET @foo = 'SELECT * from OPENQUERY(' + @LinkedServer +
', ''SELECT *
    FROM Table1
    WHERE date_revised > '''''+@myDate+'''''')'

Don't mind if the numbers of ' is off... I cut things out to make this shorter. I have tried using convert() on the SQL Server side, but I cannot seem to find a format that Oracle accepts, so it keeps throwing errors.

At a minimum, I require date, hours, and minutes. When testing values in SQL Developer (Oracle) to figure out acceptable formats, I keep running into this behavior:

select to_date('2010-11-15 12:21:00', 'yyyy/mm/dd hh:mi:ssam') from dual

15-NOV-10

Clearly, I specify I want time, but it just doesn't agree with me. I've been stuck on this issue way too long.

In short, how do I format a SQL Server datetime into a format that Oracle's to_date function will accept, and how do I make that function properly show date and time?

Improve this question
4
  • 3
    A DATE has no format. Formatting of a DATE value is done by the client, not the server (and that's what you see). If you need the output in a specific format you need to use to_char() (on the Oracle side). See here: sqlfiddle.com/#!4/d41d8/4378 Commented Nov 15, 2012 at 23:50
  • @a_horse_with_no_name In order to use @myDate as specified above, I would need to have that be a varchar right? When I typecast a datetime -> varchar, doesn't it become formatted in some manner? I tried your suggestion of using to_char instead, but I get the error: OLE DB provider "OraOLEDB.Oracle" for linked server "TED_IMCD" returned message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". The dynamic sql get evaluated to WHERE date_revised > to_char(timestamp 'Nov 15 2012 11:12AM', 'YYYY.MM.DD HH24:MI:SS') Commented Nov 16, 2012 at 0:06
  • You neeed to pass: 1. a string representing a date which is in a known format. 2. An argument to TO_CHAR which again defines that format. to_char(timestamp 'Nov 15 2012 11:12AM', 'YYYY.MM.DD HH24:MI:SS') is not implementing the prior answer. Commented Nov 16, 2012 at 5:52
  • When using timestamp '...' the format must be ISO (the way I showed it in my SQLFiddle). It's the (ANSI) SQL standard to specify date literals. You cannot use any arbitrary format for a SQL date literal like that. Commented Nov 16, 2012 at 7:21

4 Answers 4

Reset to default
2

SET @FOO = (SELECT * ...

I frown at that because you're assigning the result of a query, and * (all columns) as well, into a variable?

Anyway, a_horse_with_no_name has sort of given you the format of the datetime literal you need to present to Oracle, being timestamp.

SET @query = 'SELECT * FROM Table1
              WHERE date_revised > timestamp ''2010-11-15 12:21:00''';

(I leave the exercise of producing the YYYY-MM-DD HH:MM:SS text to you)

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

No he is building a string containing the OPENQUERY and loading it into a string variable which I guess he will later execute... this might also be introucing some confusion.
Not to be picky but I am talking about this: you're assigning the result of a query, and * (all columns) as well into a variable? . This is not the case. He is just assigning a string. He isn't assigning the result of a query. But anyway your solution is correct.
I'm not sure adding timestamp helps. Help me understand. Since the date is part of a string, it is sent over to Oracle literally. When Oracle interprets my date, it throws and error. For example, running getdate() in SQL will result in 2012-11-15 17:28:31. In Oracle, if I run select to_char('2012-11-15 17:28:31', 'DD-MON-YYYY') from dual, it throws an error:ORA-01722: invalid number 01722. 00000 - "invalid number". I assume it's trying to find data that isn't there because the datetime has been converted into a string.
You want to present the ENTIRE QUERY TEXT (all queries are presented as text, at the end of the day, unless parameters are involved) to Oracle including the "timestamp" keyword preceding a datetime literal in 'YYYY-MM-DD HH:MM:SS' format. If you can get the 2nd parameter to OPENQUERY to look exactly as shown above, it will be accepted by Oracle.
1

Assuming @mydate is DATETIME the trick is to change the datetime to a string with a known format on SQL Server and use the format to change it back to a DATE on Oracle.

SET @foo = 'SELECT * from OPENQUERY(' + @LinkedServer +
', ''SELECT *
     FROM Table1
WHERE date_revised > TO_DATE('''''
                     + CONVERT( varchar( max ), @myDate, 120 )
                     + ''''', ''''yyyy-mm-dd hh24:mi:ss'''') '')'

The 120 part tells SQL Server to format the date into a string with the format yyyy-mm-dd hh24:mi:ss, the format part tells Oracle how to turn the string back into a date.

Improve this answer

2 Comments

I'm pretty sure I tried all of the options available for formatting (1-131), an oracle didn't really like any of them. A close one was Kuwaiti format (130 or 131, but not quite...)
You can format it any way you like. You just have to 'unformat' it correctly on the other side in the TO_DATE function. There are quite a few working solutions posted here.
0

I came up with my own solution since Oracle was being uncooperative with me. I basically take the DATEPARTS of everything and construct my own date-string in the same format in oracle. Thanks for your input all. When I get more time, I will try to get this working as it should. This bandaid will do for now.

DECLARE @year varchar(4),
    @month varchar(3),
    @day varchar(2),
    @hour varchar(2),
    @meridian varchar(2),
    @minute varchar(2),
    @second varchar(2),
    @date smalldatetime,
    @OracleTime varchar(50);

SET @date = (SELECT MAX(Processed) FROM MES_CAPP.Signoff)
SET @year = YEAR(@date)
SET @month = DATENAME(m,@date)
SET @day = DAY(@date)
-------------------------------------------------------------------------
IF (DATEPART(hh, @date) >= 12)
BEGIN
    SET @hour = DATEPART(hh, DATEADD(hh, -12, @date))
    SET @meridian = 'PM'
END
ELSE
BEGIN
    SET @hour = DATEPART(hh, @date)
    SET @meridian = 'AM'
END

SET @minute = DATEPART(MINUTE, @date)
SET @second = '00'

SET @OracleTime = @day + '-'  + @month + '-' + @year + ' ' + @hour + ':' + @minute + ':' + @second + ' ' + @meridian
Improve this answer

Comments

0

I took Jeff's answer and wrapped it in a function that returns a to_date string.

    CREATE FUNCTION [dbo].[fnOracleTo_DateStringFromMsSqlDate] 
(
    @InputDate datetime
)
RETURNS varchar(100)
AS
BEGIN

    DECLARE @year varchar(4),
        @month varchar(3),
        @day varchar(2),
        @hour varchar(2),
        @minute varchar(2),
        @second varchar(2),
        @date smalldatetime,
        @OracleTime varchar(50),
        @returnString varchar(100)

    SET @year = YEAR(@InputDate)
    SET @month = Month(@InputDate)
    SET @day = DAY(@InputDate)
    -------------------------------------------------------------------------

        SET @hour = DATEPART(HH, @InputDate)

    SET @minute = DATEPART(MINUTE, @InputDate);
    SET @second = datepart(SECOND, @InputDate);

    SET @OracleTime = @day + '-'  + @month + '-' + @year + ' ' + @hour + ':' + @minute + ':' + @second ;
    set @returnString = 'to_date(' + char(39) +  @OracleTime  + char(39) + ',' + char(39) +  'DD-MM-YYYY HH24:MI:SS' + char(39) + ')'

    return @returnString;
END
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.