SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert date format in SQL sever report(BIDS) with Oracle date format


Convert date format in SQL sever report(BIDS) with Oracle date format

Author
Message
Seattlemsp
Seattlemsp
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 64
Creating a report with Oracle 9i backend database.

Setup two parameters
@startdate
@enddate

Select * from mytable
Where mytable.date Between @startdate And @enddate

I received error message bacause oracle requriment date form as 'DD-MOn-YYYY', such as '10-Jan-2012'

I test the below qury, it works in the report:

Select * from mytable
Where mytable.date Between '1-Jan-2012' And '10-Jan-2012'


What the sql should be to conver @startdate to 'DD-MOn-YYYY'?


I found a qury which can convert date to the correct format. SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-'). However it doesn't work me enven it works as stand alone qury.

So, What correct sql should be to conver @startdate to 'DD-MOn-YYYY' for my report needs?

Thanks a lot!
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28542 Visits: 39977
you parameters are datetime in the report, right, so they will convert cleanly to the DATE datatype for oracle?
It sounds like you are using varchars instead...

if your parameters are datetime, that should immediately address the issue, i believe.

if you are building a string for the SQL statement to be executed, then use the built in TO_DATE function, and tell it the format of the string.


Select *
from mytable
Where mytable.date
Between TO_DATE(@startdate,'MM-DD-YYYY')
And TO_DATE(@@enddate,'MM-DD-YYYY')



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Seattlemsp
Seattlemsp
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 64
Thanks your replying.

Follow your syntax, I received error message:

"An error occurred during local report processing,
An error has occurred during report processing,
Query execution failed for dataset "DataSet1"
ORA-01858: a non-numeric character was found where a numeric was expected"

The parameter type either as datetime or text(string) doesn't make difference.

By the way, I only used one @in front of enddate

What I may missed?
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28542 Visits: 39977
when i said parameters, i mean on the report side, sorry for the confusion.

if you are building a string and executing it from your report, without parameters, you need to follow the full oracle syntax and rules...oracle doesn't allow variables with @ in them.
I don't have oracle handy right now, but here's the basic syntax, I believe.
the param_ replacing @ is not required, iot's just a naming convention and is completely optional, i just used it for clarity.

DECLARE
param_bdate DATE;
param_edate DATE;
SET param_bdate = TO_DATE('01/15/2010','MM/DD/YYYY');
SET param_edate = TO_DATE('01/15/2010','MM/DD/YYYY');
Select * from mytable
Where mytable.date Between param_bdate AND param_edate ;



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Seattlemsp
Seattlemsp
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 64
Where should I put below code:

DECLARE
param_bdate DATE;
param_edate DATE;
SET param_bdate = TO_DATE('01/15/2010','MM/DD/YYYY');
SET param_edate = TO_DATE('01/15/2010','MM/DD/YYYY');

When I put above in the Query text area. The error message below:

Could not update a list of fields for the query. Verify that you can connect to the dat source and that your query syntax is correct.
ORA-06550: line1, colum 8:
PLS-00103: Encountered the symbol "" whenexpecting ofe of the following:

begin function package pragma procedure subteype type use <an identifier> <a dobule-quoted delimited-identifier> form current cursor.

Thanks a lot
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5461 Visits: 4639
I'm assuming the target database is Oracle and that the datatype of mytable.date is DATE.

If this is the case you have two options, option #1 is to write a stored procedure that accepts the two parameters; option #2 is to send the parameters alongside your query which arguable defeats the purpose(*).

Having said that, code below shows how to use bind-variables in your Oracle sql code.


VARIABLE STARTDATE VARCHAR2(11)
EXEC :STARTDATE := '01-JAN-2012'
VARIABLE ENDDATE VARCHAR2(11)
EXEC :ENDDATE := '10-JAN-2012'
SELECT *
FROM MYTABLE
WHERE MYTABLE.DATE BETWEEN TO_DATE(:STARTDATE, 'dd-mon-yyyy')
AND TO_DATE(:ENDDATE, 'dd-mon-yyyy')
;

Hope this helps.

(*) exception would be a really well organized environment where no literals are allowed on queries.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Seattlemsp
Seattlemsp
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 64
during the passed days, I tried many times. However it still doesn't work for me.

The error message said: ORA-00900: invalid SQL statement

It looks like I may not declare the variable or parameter right, or may not put them in the right place.

Such as can I mix the SQL statement with all other inside the qurey build window or not?

Thanks!
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5461 Visits: 4639
Seattlemsp (1/25/2012)
during the passed days, I tried many times. However it still doesn't work for me.

The error message said: ORA-00900: invalid SQL statement

It looks like I may not declare the variable or parameter right, or may not put them in the right place.

Such as can I mix the SQL statement with all other inside the qurey build window or not?


Hard to tell if you do not show us what you are doing but for sure something is wrong with the syntax, check below:

SQL>
SQL> variable startdate varchar2(11)
SQL> exec :startdate := '01-JAN-2011'
PL/SQL procedure successfully completed.

SQL> variable enddate varchar2(11)
SQL> exec :enddate := '31-DEC-2011'
PL/SQL procedure successfully completed.

SQL> select created from v$database where created between to_date(:startdate, 'dd-mon-yyyy') and to_date(:enddate, 'dd-mon-yyyy') ;
CREATED
------------------
11-AUG-11

SQL>



_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Seattlemsp
Seattlemsp
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 64
Paul ---Thanks a lot for contiuning help me.

Your code works in the SQLPlus. How can I convert it into BIDS(SSRS) format, so I can create a report in BIDS(stripdown Microsoft Visual Studio). Then the user can key in the date when run the report via SSRS.
Seattlemsp
Seattlemsp
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 64
Paul ---Thanks a lot for contiuning help me.

Your code works in the SQLPlus. How can I convert it into BIDS(SSRS) format, so I can create a report in BIDS(stripdown Microsoft Visual Studio). Then the user can key in the date when run the report via SSRS.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search