June 17, 2004 at 1:53 pm
I need to extract data from a sql server table and format the date so that oracle can read as in dd-mon-yyyy but I cannot find a style that accommodates this. What am I missing? Please help
June 17, 2004 at 2:07 pm
You can tell Oracle to recognize pretty much any format. How you do it depends on whether you actually have a date data type or a varchar display of a date.
Formatting in Oracle:
http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html
For example, I just used this a moment ago where the date was text. If you need to go to text first, use TO_CHAR. Sometimes you have to play with it.
...and TRUNC(MY_ORACLE_DT) =
TRUNC(TO_DATE('1/24/2004 12:24:54','mm/dd/yyyy hh:mi:ss'))
Formatting in SQL Server:
Some Common Formats in SQL Server:
SELECT GETDATE() AS CurrentDateTime
GO
SELECT CONVERT(CHAR(11),GETDATE(),100) AS "CurrentDateTime(Mon dd yyyy)"
GO
SELECT CONVERT(VARCHAR(15),GETDATE(),10) AS "CurrentDateTime(mm-dd-yy)"
GO
SELECT CONVERT(VARCHAR(15),GETDATE(),110) AS "CurrentDateTime(mm-dd-yyyy)"
GO
SELECT CONVERT(VARCHAR(15),GETDATE(),12) AS "CurrentDateTime(yymmdd)"
GO
SELECT CONVERT(VARCHAR(15),GETDATE(),112) AS "CurrentDateTime(yyyymmdd)"
GO
SELECT CONVERT(VARCHAR(15),GETDATE(),11) AS "CurrentDateTime(yy/mm/dd)"
GO
SELECT CONVERT(VARCHAR(15),GETDATE(),111) AS "CurrentDateTime(yyyy/mm/dd)"
GO
SELECT CONVERT(VARCHAR(15),GETDATE(),101)AS "CurrentDateTime(mm/dd/yyyy)"
GO
SELECT CAST(CONVERT(VARCHAR(15),GETDATE(),101) AS DATETIME)AS "TruncAndCastDateTime"
GO
SELECT CAST(CONVERT(VARCHAR(15),GETDATE(),101) AS SMALLDATETIME)AS "TruncAndCastSmallDateTime"
GO
[font="Courier New"]ZenDada[/font]
June 17, 2004 at 2:12 pm
That's my problem. I've tried all those SQL Server methods you've listed and none of them will get me to dd-mon-yyyy. I need to put the date in this format to match an existing extract.
June 17, 2004 at 3:28 pm
Push from SQL Server to your ascii extract with this:
select convert(varchar(25), getdate(), 113)
My instance of Oracle reads this as a date without using any to_char or to_date functions.
Be sure to look at the web page I referred you to earlier. You really have an Oracle question, not a SQL Server question. What text display Oracle likes for dates depends on your configuration of Oracle. You may still need to use to_char and to_date Oracle functions. You are probably uploading your extract to one DBMS or the other. You aren't going to leave your date text as varchar anyway, right? You will want to cast back to date.
[font="Courier New"]ZenDada[/font]
June 17, 2004 at 3:40 pm
Look up "date formats" in BOL. You can get all the pre-fab constants in a ref titled, "CAST AND COVERT" in the T_SQL library.
If you a dumping your extract from DTS, you can also specify your own format. Read about it in "Date Time String Transformation" in the Data Transformation Services library.
[font="Courier New"]ZenDada[/font]
June 18, 2004 at 11:23 am
Thanks but that didn't work either. Here's what I came up with and it works like a charm since there was nothing standard:
create function OraDateFormat (@theDate datetime)
returns varchar(20) as
begin
declare @res varchar(20)
select @res = right('0' + convert(varchar(2), datepart(dd, @theDate)) ,2 )
select @res = @res + '-' + right('0' + convert(varchar(3), datename(month, @theDate)) ,3 )
select @res = @res + '-' + convert(varchar(4), datepart(YYYY, @theDate))
return @res
end
Go
June 18, 2004 at 6:26 pm
try:
SELECT REPLACE(CONVERT(varchar(11),@theDate,113)' ','-')
06/17/2004
becomes
17-Jun-2004
much simpler than the function above
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply