Populating a datetime field from containerstarttime in ssis 2008

Martin Griffiths
Martin Griffiths used Ask the Experts™
I'm creating a SQL string in an expression to populate a table including population of a datetime field with the value of the container start time in an ssis package. Problem is I'm having to convert the system::containerstarttime variable to a string so that it can be appended to the rest of the SQL string text. The date then goes in as '18/04/2012 09:05:23'. Using this value in my insert sql statement results in the following error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

How can I get this date in to a datetime field please?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you need to chop the string around into ISO format

SET @X = '18/04/2012 09:05:23'

SET @X = SUBSTRING(@X, 7,4) + SUBSTRING(@X,4,2) + SUBSTRING(@X,1,2) + RIGHT(@X,9)

e.g 20120418

While you are doing your insert statement, at that time you should convert the given string in proper format. It seems that you are not having DMY format. You can do this by executing the statement

Open in new window

Otherwise, You can try converting the value as below:
convert(char(10), @myString, 103) + ' ' + convert(char(8), @myString, 108)

Open in new window

Change @myString with your column of variable name containing the data '18/04/2012 09:05:23'.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial