date problem : dynamic SQL in an OLE DB Source component

Hello,

I have an OLEDB Source component with a Oracle OLEDB connection manager.
In my SQL statement I must do something like this ...

SELECT * FROM OracleTable
WHERE convert(datetime, OracleDate) = parameter

I've dynamicaly build the statement like this : http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

Unfortunately this doesn't work because the variable is a datetime value and not a string.

Maybe another way is to store the date in a table in my SQL database and include it in my Oracle SQL statement ? This would mean that there are multiple connection managers being used in one SQL statement ... I have no idea if this would be possible ...

Any other options ?

Yes that would be possible but is a far far more complicated solution than it needs to be.

Keep persuing the expression solution. If the variable is a DateTime then you need to cast it as a string. Remember - the expression is populating a string property so it should return a string.

This is a string manipulation problem - pure and simple!

-Jamie

|||thanks, it works ... I now convert the date to integer format and then to string format so it can be used in the dynamic SQL query

Date Parameter with ReportViewer

Hi All,

I have the following code which works using the ReportViewer component. However how do I pass in the current date i.e Now?

When I use DateTime.Today.ToString I get the following error: The value provided for the report parameter 'SnapshotDate' is not valid for its type. SnapshoteDate is defined as DateTime.

If Not IsPostBack Then
ReportViewer1.ServerUrl = "http://devnet2/ReportServer"
ReportViewer1.ReportPath = "%2fCorVuReports/LAA/Pie&SnapshotDate=06/01/2004"
ReportViewer1.Toolbar = Microsoft.Samples.ReportingServices.ReportViewer.multiState.False
ReportViewer1.Zoom = "75"

End If

I managed to solve this...

cheers

|||

Could you please post your resolution, so others (like us) who encounter the problem could benefit from your wisdom.

Much appreciated.

Date Lookups

I want to do a lookup on date column. My lookup date is of type smalldatetime, and my date is of type datetime (date with time component). My lookup is failing because of incompatible data types.

How do I perform the lookup with date columns having date and time components?

Switch to your source component to use a query and then use the cast operator to cast the datetime column to smalldatetime in the query of you source component.

Rafael Salas

Date lookup in SSIS

How do I perform a date lookup in SSIS. I have a date with time component in it. This has to be looked-up with a table that contains only a date element.

You need convert the fields into varchar and do the comparison or you can convert both the fields to similar date formatted datetime type and do the comparison.

Thanks,

S Suresh

|||

I tried converting to varchar and it does not work well. There should be some other elegant way of doing this. To help understand the problem, I have created two tables table_1 and table_2. Table_1 is the source table with one column DateWithTime of type (datetime). Table_2 is the lookup table with columns DateSK of type (int) and another column DateAlone of type (smalldatetime).

I am taking the column DateWithTime from table_1 and looking it up with DateAlone from table_2 to get DateSK.

I do not know the right way to lookup date fields. Should I compare day, month and year separately to get DateSK.

Thanks,

Vijay

|||

I commonly use a slight cheat on this one, if you make the integer key of your lookup table the difference in days from 1 Jan 1900 then you can calculate the key instead of looking it up.

You can also use the same trick with the time portion of neccessary (do the diff in seconds).

Hope that helps you

Philip

|||

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

|||

mike.groh wrote:

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

Being from the UK mm/dd/yyyy does not mean too much to me as we use dd/mm/yyyy, this makes string based manipulation of date ambiguous as 01/05/2006 is either the 1st May or 5th Jan. This can either be made unambiguous by using ISO date format yyymmdd or is it yyyy-mm-dd, I can't remember offhand what the format code is for that I think it might be 121. or using names for months instead of numbers.

The reason I use the method I have already posted on this thread is it overcomes this ambiguity and provides a fast way of identifying the correct key for dates and times, which I think was the purpose of the original post.

|||

Philip Coupar wrote:

mike.groh wrote:

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

Being from the UK mm/dd/yyyy does not mean too much to me as we use dd/mm/yyyy, this makes string based manipulation of date ambiguous as 01/05/2006 is either the 1st May or 5th Jan. This can either be made unambiguous by using ISO date format yyymmdd or is it yyyy-mm-dd, I can't remember offhand what the format code is for that I think it might be 121. or using names for months instead of numbers.

The reason I use the method I have already posted on this thread is it overcomes this ambiguity and provides a fast way of identifying the correct key for dates and times, which I think was the purpose of the original post.

yyyy--mm-dd is unambiguous.

Date Conversion inconsistent with DB rules

I'm trying to validate data entered from users as legitimate dates by running them thru a conversion component. When conversion fails, I asssign NULLs, other wise land the date to a datetime field on the database. Dates that pass thru the conversion, however fail on insert into the DB.

It appears that the rules in the conversion component are different than that of the DB?

for instance if a user forgot the last digit on a year, i.e. "10/22/197" the convert creates a field for any of the various date datatypes as 10/22/0197, which fails on imsert into the DB, becasue it won't allow years prior to 1753.

Dates are very piccy, it is best to validate and make sure the date is correct in your package, in addition some rdms support dates before 1753 which is why SSIS supports them, SQL doesn't.