I am using Execute sql task in my SSIS package, and I am trying to make the following query:<o:p></o:p>

Select max(sqlid) from archive.dbo.Archivebbxfbhdr
where timein <= ?<o:p></o:p>

Where ? is my input parameter variable migration_start which is a datetime.<o:p></o:p>

My issue is that variable name migration_start which give me default format of 6/11/2015 1:26 AM

But I expecting to get in 2015-06-11 01:26:22.813 format.<o:p></o:p>

How I can I change the datetime format of my variable to be (yyyy/mm/dd)hh:mm:ss)?<o:p></o:p>

Thanks.<o:p></o:p>

Use a Derived Column Expression with a SSIS expression

I started with the year and month

SUBSTRING((DT_WSTR, 200)@[User::StringVar],FINDSTRING((DT_WSTR, 200)(@[User::StringVar]),"/",2)+1,4) +"/"+ RIGHT("0"+SUBSTRING((DT_WSTR, 200)@[User::StringVar],FINDSTRING((DT_WSTR, 200)(@[User::StringVar]),"/",1)-1,1),2) 

But you can continue in this manner for the rest of the value.

you can just make it like this

(DT_WSTR,30) GETDATE()

to get it in your format

Hi Vipin,

In SQL Server Integration Services, the DateTime data type variable doesnt support milliseconds, Milliseconds are truncated.

To achieve your goal, please change the variable data type to VARCHAR in Parameter Mapping pane, then change the query like below:
Select max(sqlid) from archive.dbo.Archivebbxfbhdr
where timein <=Convert(DateTime, ?,121)

The following blog about work around for missing milliseconds in SSIS datetime variable is for your reference:
http://surendrathotamsbiproffesional.blogspot.com/2014/07/ssis-2012-work-around-for-missing.html

Thanks,
Katherine Xiong