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.