Trouble with Date concatenation with varchar2 string in oracle

Posted on 2008-10-26
Last Modified: 2013-12-07
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; 
 -- 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 ';
		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

Question by:cmrnp
LVL 10

Accepted Solution

dbmullen earned 500 total points
ID: 22810072
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
v_sql varchar2(3000);
v_strtmp varchar2(100) := 'table';
v_fromdate date := trunc(sysdate);
v_todate   date := trunc(sysdate) + 1;
yadda number(10) := 0;
         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'')';
         v_sql := ' ' ;
         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;
         dbms_output.put_line(to_char(yadda) );

Open in new window


Author Closing Comment

ID: 31510189

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

Thanks heaps.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question