SSIS: Conversion failed when converting date and/or time from character string

Posted on 2014-07-17
Last Modified: 2016-02-11
Hi All,

I have a problem.

I'm trying to insert a date value (which is a sting) into a date column (which is a date data type). The date is read from the file creation property of a file using the script task in C# as follows:

strFileDate = (FileObject.CreationTime.ToShortDateString)
        strFileTime = (FileObject.CreationTime.ToShortTimeString)

Open in new window

I pass the date and time to variables in SSIS and then from there I use the SQL task to try to insert the date and time variables into the SQL server table as follows:

INSERT INTO ag_opendatetime (OpenDate, OpenTime) SELECT '', ''

Open in new window

In the SQL task in Expressions I have set the following to read the values of the variables as follows:

"INSERT INTO ag_opendatetime (OpenDate, OpenTime) SELECT '"+ @[User::FileDate]+"', '"+@[User::FileTime] +"'"

Open in new window

Eventually I get the error
SSIS: Conversion failed when converting date and/or time from character string

I've tried every convert(date, cast( etc and it doesn't work. How can I make SQL server accept the string date and or time values?


Question by:onesegun
    LVL 14

    Accepted Solution


    In place of passing variable in the Execute SQL Task you can Write insert query like this

    INSERT INTO [dbo].[CallData]
    And then Map the parameter it will work.

    I tested this and there is no issue with it.

    Author Closing Comment

    Fantastic! That works. Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    Over the last 2 years, I have been working on SSIS 2008. Really the tough tasks in SSIS are to deploy packages and pass parameters (Values from outside package). The latter is certainly a headache for developers, particularly for me. We had to ma…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now