Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

calling date functions in oracle sql via sqlserver 2005 linked server

Hi,
      I connect to an Oracle database via a linked Server in SQL Server 2005.
I call a function in Oracle
ARADMIN.fn_adjusted_date(ac.SCHEDULED_START_DATE) as Scheduled_Start_Date,
That returns a date format in the following format
Mar 26 2011  1:00AM

Is there a function I can call in Oracle that will return the following format
Mar 26 2011  13:00

thanks
0
blossompark
Asked:
blossompark
  • 7
  • 3
  • 3
2 Solutions
 
Nico BontenbalCommented:
Use the SQL server date functions for this. Use:
convert(varchar(25),ac.SCHEDULED_START_DATE,100)
See:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
for other possible formats.
0
 
blossomparkAuthor Commented:
Hi Nicobo,
thanks...will try this out and let you know result
0
 
blossomparkAuthor Commented:
Hi Nicobo,
 no matter what value I put in for style (eg 112 below)
SELECT
convert(varchar(25),Scheduled_Start_Date,112),

it returns the  same format eg
Mar  5 2011  2:00PM
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
johanntagleCommented:
It might be the ARADMIN.fn_adjusted_date that's doing the formatting?  Anyway, in Oracle you format date for output using the to_char function.  To get an output like Mar 26 2011  13:00 you need

to_char(ac.scheduled_start_date,'MON DD YYYY HH24:MI')

0
 
blossomparkAuthor Commented:
Hi johanntagle, will try that and post  the result...thank you
0
 
Nico BontenbalCommented:
Is Scheduled_Start_Date a string or a date? Based on your results I guess it is a string. The first step is to convert it back to a datetime. This can be done by:
select CONVERT(datetime,Scheduled_Start_Date,100)
From this result you can get to your desired format like this:
select convert(varchar(25),CONVERT(datetime,Scheduled_Start_Date,100),107) + ' ' + convert(varchar(25),CONVERT(datetime,Scheduled_Start_Date,100),108)
Add the replace function to eliminate the extra , in the result.
0
 
blossomparkAuthor Commented:
Hi johanntagle,
getting a syntak error when I try  that
SELECT  * FROM OPENQUERY(STANDBY,
'SELECT
 /*
ARADMIN.fn_adjusted_date(ac.SCHEDULED_START_DATE) as Scheduled_Start_Date,
*/
to char(ac.scheduled_start_date,'MON DD YYYY HH24:MI') as Scheduled_Start_Date,

Line 9: Incorrect syntax near 'MON'.
0
 
blossomparkAuthor Commented:
Hi Nicobo,
 Scheduled_Start_Date  is a a varchar, will try your suggestion and get back to you
0
 
Nico BontenbalCommented:
You typed 'to char' (to<space>char), this should be to_char (to<underscore>char).
0
 
johanntagleCommented:
Oh SCHEDULED_START_DATA is a varchar!  Then the Oracle version of Nicobo's solution is:

to_char(to_date(ac.SCHEDULED_START_DATE,'MON DD YYYY  HH:MIAM),'MON DD YYYY HH24:MI')

Note that I intentionally put two spaces between YYYY and HH, since the sample data you gave also has two spaces between those two fields.

Thank you Nicobo for clarifying the underscore for me.
0
 
johanntagleCommented:
Oops I'm missing a closing right parenthesis there.  Should be:

to_char(to_date(ac.SCHEDULED_START_DATE,'MON DD YYYY  HH:MIAM),'MON DD YYYY HH24:MI'))
0
 
blossomparkAuthor Commented:
still getting syntax error with
to_char(to_date(ac.SCHEDULED_START_DATE,'MON DD YYYY  HH:MIAM'),'MON DD YYYY HH24:MI')),
Line 6: Incorrect syntax near 'MON'.

do the ' need to be escaped?
this query is going to oracle via an OPENQUERY statement in a linked server in sql server 2005

nicobo this solution works,
select CONVERT(datetime,Scheduled_Start_Date,100)
From this result you can get to your desired format like this:
select convert(varchar(25),CONVERT(datetime,Scheduled_Start_Date,100),107) + ' ' + convert(varchar(25),CONVERT(datetime,Scheduled_Start_Date,100),108)
Add the replace function to eliminate the extra , in the result.

but i'm hoping to fix it at the oracle end as i will have a lot less recoding to do!!
however if that cant happen then this is a great solution anyway...:-)
0
 
blossomparkAuthor Commented:
Hi nicobo and johanntagle...thanks for your inputs, much appreciated!!
i'm going to run with nicobo's solution, a bit more typing but it works!! thanks again
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 7
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now