none
T-SQL "DATE" equals to "DateTime" in SSIS variable type?

    Question

  • Hello,

    I have a table column of "DATE" type, which has normal dates as well as"0001-01-01" and "9999-12-31".

    I would like to read one of the these values to an SSIS variable. What should be the data type of it?

    SSIS's DateTime cannot read T-SQL "DATE".

    Tuesday, February 19, 2013 12:45 AM

Answers

  • In SSIS, create a variable of type string and use it to store date type values that you get from table.

    Thanks. That seems to work. I can cast it to DT_DATE for comparing against other SSIS DateTime variables.

    • Marked as answer by James_SM Tuesday, February 19, 2013 4:13 AM
    Tuesday, February 19, 2013 4:13 AM

All replies

  • Yeah, SSIS Data Types are not mapped one-to-one to SQL Data Types. Does it not do an implicit conversion from DATE to DATETIME in SSIS?

    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Tuesday, February 19, 2013 2:00 AM
  • Does it not do an implicit conversion from DATE to DATETIME in SSIS?

    No implicit conversion according to what I can see.

    select convert(date,'2010-01-01') as MyDate

    An SSIS "DateTime" variable cannot read the above value.
    Error Message:

    Error: An error occurred while assigning a value to variable "xxxxx": "The type of the value being assigned to variable "User::xxxxx" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

    select convert(datetime,'2010-01-01') as MyDate

    The above works with SSIS's DateTime variables, but I have dates like '0001-01-01' in the table which can't be converted to datetime.


    • Edited by James_SM Tuesday, February 19, 2013 2:35 AM
    Tuesday, February 19, 2013 2:33 AM
  • Hi James,

    It would implicitly cast it to the datetime if you are only giving date fomat.

    It depends what you are trying to do if you are using it further for a table in date column it would cast it again at the time of insertion.

    Can you please elaborate how you want to use this after storing it into varaible.

    Please mark it as helpful if it helps.

    Thanks

    Sumit

    Tuesday, February 19, 2013 3:49 AM
  • It depends what you are trying to do if you are using it further for a table in date column it would cast it again at the time of insertion.

    Can you please elaborate how you want to use this after storing it into varaible.

    Hi Sumit,

    I have a table which maintains the 'last loaded date' (SQL Server Data type: Date) against a record.

    In SSIS, using an 'Execute SQL Task', I return this 'last loaded date' as a ResultSet (Single Row) which has to be captured into a SSIS variable. Later in the same package, I have to compare this 'last loaded date' against another SSIS DateTime variable to decide which tasks to do.

    The problem is, SSIS Variables only have "DateTime" type for all date-time related values. T-SQL's Datetime type maps to SSIS's "DateTime", however, T-SQL's "Date" doesn't, probably because it has values like '0001-01-01'

    I don't see any implicit conversion of T-SQL's "DATE" to SSIS's DateTime. I have listed the error message I get above.

    Tuesday, February 19, 2013 4:03 AM
  • Hi James,

    In SSIS, create a variable of type string and use it to store date type values that you get from table.


    Thanks, hsbal

    Tuesday, February 19, 2013 4:04 AM
  • In SSIS, create a variable of type string and use it to store date type values that you get from table.

    Thanks. That seems to work. I can cast it to DT_DATE for comparing against other SSIS DateTime variables.

    • Marked as answer by James_SM Tuesday, February 19, 2013 4:13 AM
    Tuesday, February 19, 2013 4:13 AM
  • Hi,

    You cast it as either a CHAR(10) (meaning "yyyy-mm-dd") or to a DATETIME.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, February 19, 2013 1:59 PM