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: 2406
  • Last Modified:

Trouble with Date concatenation with varchar2 string in oracle

Hi
I'm trying to concatenate dates with other Sql statements so I can place common Where statements in 1 procedure.

I am running into troubles with the date.
What is happening is To and From dates are in format dd-mm-yyyy as in 10-01-2008

When I concatenate into v_SQL which is varchar2 it becomes dd-MONth- YY as in 10-JAN-08

Can somebody please explain why and how to fix?
-- v_inputdate is varchar2
 
v_SQL varchar2(1000);
 v_strTmp varchar2(100);
 v_WhereSet number;
 v_ToDate DATE;
 v_FromDate DATE; 
 
 BEGIN
 
 -- Input Date
 
  If Length(v_InputDate) > 0 Then
 
  -- Date that is input is 10th January 2008
 
	v_FromDate := To_Date(v_InputDate,'dd-mm-yyyy');
-- I am now 10-01-2008	
 
If v_ReportType = 'D' Then
		v_ToDate := v_FromDate + 1 ;
-- I am now 11-01-2008
 
	 End If; 
	 
	 If v_WhereSet = 1 Then
		v_strTmp := ' AND ';
	 Else
		v_WhereSet := 1;
		v_strTmp := ' WHERE  ';		
	 End If;
	 v_SQL := v_SQL || v_strTmp || ' pr.pres_dt >= ' || v_FromDate || 
				 ' AND pr.pres_dt < '  || v_ToDate;   
-- At this point v_SQL changes to have v_FromDate as 10-JAN-08  
-- My data is stored as dd-mm-yyyy.
 
  End If;

Open in new window

0
cmrnp
Asked:
cmrnp
1 Solution
 
dbmullenCommented:
well..   you're really making things hard on your self...

hard way:
         v_SQL := v_SQL || v_strTmp || ' pr.pres_dt >= to_date(''' || to_char(v_FromDate,'yyyymmdd') ||
                                ''',''yyyymmdd'')  AND pr.pres_dt < to_date('''  || to_char(v_ToDate,'yyyymmdd') || ''',''yyyymmdd'')';

easy way:
         v_sql := 'select count(*) from use_binds pr where pr.pres_dt >= :v_from_date and pr.pres_dt < :v_todate';        
         execute immediate v_sql into yadda using v_fromdate, v_todate;



create table use_binds as select sysdate pres_dt from dual;
 
set serveroutput on
declare 
v_sql varchar2(3000);
v_strtmp varchar2(100) := 'table';
v_fromdate date := trunc(sysdate);
v_todate   date := trunc(sysdate) + 1;
yadda number(10) := 0;
 
begin
         v_SQL := v_SQL || v_strTmp || ' pr.pres_dt >= to_date(''' || to_char(v_FromDate,'yyyymmdd') || 
                                ''',''yyyymmdd'')  AND pr.pres_dt < to_date('''  || to_char(v_ToDate,'yyyymmdd') || ''',''yyyymmdd'')';
                                
         dbms_output.put_line(v_sql);
         
         v_sql := ' ' ;
         v_sql := 'select count(*) from use_binds pr where pr.pres_dt >= :v_from_date and pr.pres_dt < :v_todate';
         dbms_output.put_line(v_sql);
         
         execute immediate v_sql into yadda using v_fromdate, v_todate;
         dbms_output.put_line(to_char(yadda) );
         
end;                                

Open in new window

0
 
cmrnpAuthor Commented:
Brilliant

Most questions i've had have not been answered but this was spot on.

Thanks heaps.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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