What you could also do is make the value on each side be a 'YYYYMMDD' string. SQL Server gets them right every time. (It's the best date literal format). On the SQL Server lookup side, you can get that by CONVERT(varchar(8), column, 112)
Date Datatype Issue in the Lookup in SSIS
Hello,
I map "date" information obtained from the Excel source in SSIS (1.3.2 version) through the Data Conversion Transformation Editor with the 'database date' data type
In Date Lookup Transformation Editor, I use the Date_Dim table and the data type of the full_date attribute in this table is "date" as well. However, I get a warning like the one below.
I
interestingly full_date can be matched with Copy of competiton_id in the Unicode String DT-WSTR. Could you help me, please?
*I also tried the following SQL commands in Lookup, but it does not work.
SELECT
DateKey,
CAST(full_date AS DATE) AS full_date,
day_name,
month,
year
FROM
Date_Dim;
2 answers
Sort by: Most helpful
-
Greg Low 1,495 Reputation points Microsoft Regional Director
2024-03-07T21:56:02.7+00:00 -
Mustafa SEVER 0 Reputation points
2024-03-09T21:44:51.6433333+00:00 Normally, I don't need information in the format hh:mm:ss within the date for my project. I used database timestamp instead of date[DT-DBDATE] in the Data Conversion Transformation Editor for the issues.
I executed the following commands in Date Lookup. I mapped Copy of Date to full_date. and SSIS ran successfully
SELECT
DateKey, CAST(full_date AS smalldatetime) AS full_date, day_name, month, year ```FROM
Date_Dim;