[RESOLVED] Date Parameter in OracleParamater

[danasegarane]

Hi all,
I am using this SQL statement to fetch data from oracle

str = "select * from Master where CreatedDttm = TO_DATE(:p_StartDate,'MM/DD/YYYY')"

and passing the date to oracle parameter as

 oParameter.ParameterName = "p_StartDate"
        oParameter.Value = "01/01/2009"
        oParameter.OracleType = OracleType.DateTime
        oParameterList(0) = oParameter

And executing this command I getting error that

Invalid varriable name or number ?

What I am doing wrong here ?

I did the steps mentioned in post. But the same error

[danasegarane]

This also produces the same error...

         str = "select * from Master where CreatedDttm = :startDate"
        oParameter.ParameterName = "p_StartDate"
        oParameter.OracleType = OracleType.DateTime
        oParameter.Value = DateTime.ParseExact("01/01/2009", "MM/dd/yyyy", CultureInfo.InvariantCulture)
        oParameterList(0) = oParameter

[danasegarane]

Without DateConversion it is working...

str = "select * from Master where CreatedDttm > :p_StartDate"

But I need to format it as MM/DD/YYYY hh:mm:ss AM

[Pradeep1210]

What happens if you substitute @ instead of :

str = "select * from Master where CreatedDttm = TO_DATE(@p_StartDate,'MM/DD/YYYY')"

[danasegarane]

What happens if you substitute @ instead of :

str = "select * from Master where CreatedDttm = TO_DATE(@p_StartDate,'MM/DD/YYYY')"

I am getting the same Error "Illegal Varriable/Number "

str = "select * from vehicleMaster where CreatedDttm =TO_DATE(@p_StartDate,'MM/DD/YYYY')"

[Pradeep1210]

I think you would need to use the conversion in the SELECT list, since you are already passing a date value in the WHERE clause anyways.

str = "select *, TO_DATE(CreatedDttm, 'MM/DD/YYYY') from Master where CreatedDttm = :p_StartDate"

[danasegarane]

I have the User Enter the date in format dd/MM/yyyy and I need to convert, before matching with the table.

[Pradeep1210]

Your CreatedDttm is a date column, and you can't alter how it is stored internally. A date column is a date column for the database, irrespective of how it got it. Since you want the output in dd/MM/yyyy format, you would specify it in the SELECT list.
Since you have already specified that the parameter you are passing is of date type (oParameter.OracleType = OracleType.DateTime), that should be sufficient.

[abhijit]

Are you using ODP.NET?

[abhijit]

Without DateConversion it is working...

str = "select * from Master where CreatedDttm > :p_StartDate"

But I need to format it as MM/DD/YYYY hh:mm:ss AM

You should be able to use it with the timestamp. I tried something very similar using ODP.NET.

OracleConnection oConn;
            OracleCommand oCmd;
            OracleParameter oPrm;
 
            string strSQL = "SELECT lease_no, lease_create_date, creation_date, customer_name," + 
                                "lease_status FROM lease_header WHERE "+
                                "lease_create_date > :p_startdate";
            try
            {
                oConn = new OracleConnection();
                oConn.ConnectionString = "Data Source=mydb;User Id=abhijit;Password=xfiles;";
                oConn.Open();
                Console.WriteLine(oConn.State);
                oCmd = new OracleCommand();
                oCmd.CommandText = strSQL;
                oCmd.CommandType = System.Data.CommandType.Text;
                oCmd.Connection = oConn;
                oPrm = oCmd.CreateParameter();
                oPrm.ParameterName ="p_startdate";
                oPrm.OracleDbType = OracleDbType.Date;
                oPrm.Value = DateTime.Parse("4/9/2010 10:17:17 AM");
                oCmd.Parameters.Add(oPrm);
                Console.WriteLine(oCmd.CommandText);
                using (OracleDataReader reader = oCmd.ExecuteReader())
                {
                    // Always call Read before accessing data.
                    while (reader.Read())
                    {
                        Console.WriteLine("{0}:{1}:{2}:{3}", reader[0], reader[1], reader[2], reader[3]);
                        
                    }
                }
 
                oConn.Dispose();
            }

[danasegarane]

You should be able to use it with the timestamp. I tried something very similar using ODP.NET.

OracleConnection oConn;
            OracleCommand oCmd;
            OracleParameter oPrm;
 
            string strSQL = "SELECT lease_no, lease_create_date, creation_date, customer_name," + 
                                "lease_status FROM lease_header WHERE "+
                                "lease_create_date > :p_startdate";
            try
            {
                oConn = new OracleConnection();
                oConn.ConnectionString = "Data Source=mydb;User Id=abhijit;Password=xfiles;";
                oConn.Open();
                Console.WriteLine(oConn.State);
                oCmd = new OracleCommand();
                oCmd.CommandText = strSQL;
                oCmd.CommandType = System.Data.CommandType.Text;
                oCmd.Connection = oConn;
                oPrm = oCmd.CreateParameter();
                oPrm.ParameterName ="p_startdate";
                oPrm.OracleDbType = OracleDbType.Date;
                oPrm.Value = DateTime.Parse("4/9/2010 10:17:17 AM");
                oCmd.Parameters.Add(oPrm);
                Console.WriteLine(oCmd.CommandText);
                using (OracleDataReader reader = oCmd.ExecuteReader())
                {
                    // Always call Read before accessing data.
                    while (reader.Read())
                    {
                        Console.WriteLine("{0}:{1}:{2}:{3}", reader[0], reader[1], reader[2], reader[3]);
                        
                    }
                }
 
                oConn.Dispose();
            }

I tried the TimeStamp Column . But It was not working. I change the type to Varchar. And it worke

 '
        str = "select * from vehicleMaster where CreatedDttm > TO_DATE(:p_StartDate,'MM/DD/YYYY HH24:Mi:SS')"
        oParameter.ParameterName = "p_StartDate"
        oParameter.OracleType = OracleType.VarChar
        oParameter.Value = "01/01/2009" 
        oParameterList(0) = oParameter

[abhijit]

What happens if you change to Date instead of a varchar?

[danasegarane]

What happens if you change to Date instead of a varchar?

I am getting the same Error "Illegal Varriable/Number "

[abhijit]

What happens if you change to Date instead of a varchar?

I am getting the same Error "Illegal Varriable/Number "

You would need to get rid of the TO_DATE function in your Oracle query.

[danasegarane]

You would need to get rid of the TO_DATE function in your Oracle query.

No I cannot . I need to format the data as 24 hr format and check

[abhijit]

No I cannot . I need to format the data as 24 hr format and check

Agreed. What I am saying is that if you get rid of the TO_DATE and use the parameter type OracleDbDate to assign a date value with a timestamp, it will work.

That's an alternative solution. I tried that out here and it works correctly.

[danasegarane]

That I have tried in Post Number #3