Using DateTime with SQL

  • Thread starter Thread starter Herby
  • Start date Start date
H

Herby

I am using instances of DateTime within my application
and i need their values to be used as selection criteria in an SQL
select clause.

So i need them to return a literal string representation of themselves
in the following format


'{ts '1990-01-01 00:00:00'}'


Where the date can be any valid date as contained in the DataTime
instance.


How can i get them to do this ?
 
Please help im stuck on this?

The database server expects it in the following format
'{ts '1990-01-01 00:00:00'}'

But Datetime does not seem to provide a method to return it in this
universal format.

When i use a DataTime object as a DbParameter - i get similar problems.

Why all these problems with Dates still all the time?
 
I cannot always use DbParameters.
My application is highly dynamic sometimes i will be dealing with array
of source values and set membership SQL commands
E.g

SELECT * FROM Members WHERE MemberNo IN ( @membNo )

The variable membNo will have a different number of items when
executing the above, so its better to use a literal expression here

IN( 1,23,56,67,89, 9 )

Another time it could be

IN( 9 )

I need to support both Oracle and SQL servers, hopefully with common
code.

I have got over my initial problem by using a format specifier:
myTextDateInstance.ToString( "yyyy-MM-dd hh:mm:ss");

This gets the date part in the necessary format.
I can then add the {ts ' '} part via StringBuilder etc.

My problem now is the System.Data.OracleClient is throwing an exception
ORA-00911 Invalid character.
And its to do with the date part

WHERE effective_date <= {ts '1990-01-01 12:00:00'}

But this works fine when i run against the target database
independently.

So the Oracle driver does now not like the above format, but thats what
the Oracle DBMS is expecting it.
As that is the type of the column effective_date DATE.

Can anyone help me?
 
Back
Top