Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Convert date format in SQL sever report(BIDS) with Oracle date format Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 4:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, 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!


Post #1233620
Posted Tuesday, January 10, 2012 8:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1233692
Posted Wednesday, January 11, 2012 12:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, 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?

Post #1233751
Posted Wednesday, January 11, 2012 5:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1233899
Posted Wednesday, January 11, 2012 2:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, 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
Post #1234366
Posted Wednesday, January 11, 2012 2:49 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1234402
Posted Wednesday, January 25, 2012 10:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, 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!

Post #1241923
Posted Thursday, January 26, 2012 6:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1242104
Posted Friday, January 27, 2012 10:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, 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.
Post #1243021
Posted Friday, January 27, 2012 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:06 PM
Points: 32, 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.
Post #1243040
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse