.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Convert SSIS DateTime to a String

Posted By:      Posted Date: September 21, 2010    Points: 0   Category :Sql Server

Being a newbie to SSIS I'm not sure of the most efficient method of converting a DateTime object to a String.


I'm from a C# background where this would be easy using DateTime.ToString("YYYYMMdd"). I want to use the date in a file name so don't require most of the parts.


I'm sure I could do this using a script task to produce a file name for each row of data in my table and add that filename to the dataset but it seem like overkill to do something that should be simple. Also as I'm supposed to be getting to grips with SSIS I shouldn't keep running back to what I know.


My current approach is to derive a column and build up an expression to convert the date into a string. The only problem being that it doesn't work.

The expression I'm working with is:

(DT_WSTR, 50)([OrgName] ) + "_" + (DT_WSTR, 50)( [PayrollName] ) + (DT_WSTR, 4)(YEAR( [ProcessedDate] )) + (DT_WSTR, 2)(MONTH( [ProcessedDate] )) + (DT_WSTR, 2)(DAY( [ProcessedDate] )) ".txt"


Can anyone see where I'm going wrong?


All comments greatly received.

View Complete Post

More Related Resource Links

How do I convert a Sql Datetime value into a string including the miliseconds?


 Hi all,

Currently I am able to bring the datetime from my SQL table but I also need the milisecods. Here is the line of code:

DateTime latestTimeStamp = DateTime.UtcNow;
string select = "select max(timeStamp) from Table1";

SqlConnection myConnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["UctConnectionString"].ConnectionString);
SqlCommand mySqlCommand = new SqlCommand(select, myConnection);

latestTimeStamp = (DateTime)mySqlCommand.ExecuteScalar();

So latestTimeStamp = {9/22/2010 6:24:09 PM} but I needed it to be latestTimeStamp = {9/22/2010 6:24:09:080 PM}
since the SQL field value is 2010-09-22 18:24:09.080 and I want to do an accurate datetime comparison.

Thank you

convert datetime to Numeric in ssis

I have a source with datetime datatype and destination as numeric. how can i do that using dataconversation or anything with help i tried with convert funtion its not working . can anyone help . thanks

Convert String "" To datetime


I have a problem about convert a string to datetime.

in my database i have table with canceldate column.

that column have a empty row (like '' but not null ) and string (like '01-12-2004').

when i run query this message was show "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

if i use this query "convert(datetime,canceldate,105)" the empty row was convert to 1900-01-01 00:00:00.000.

i want convert that coloumn without change value of empty row.

any idea?




How can i convert Integer to String in SSIS Derived column.


i have gender column with 0,1 values

i`ve to convert it into 0=male and 1=female,i tried this


BUT it is giving error

TITLE: Microsoft Visual Studio

Error at Data Flow Task [Derived Column [46]]: The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Error at Data Flow Task [Derived Column [46]]: Attempt to set the result type of binary operation "Gender == 0" failed with error code 0xC0047080.

Error at Data Flow Task [Derived Column [46]]: Computing the expression "[Gender] ==0?"Male":[Gender]==1?"Female":"NA"" failed with error code 0xC0047084. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.

Error at Data Flow Task [Derived Column [46]]: The expression "[Gender] ==0?"Male":[Gender]==1?"Female":"NA"" on "input column "Gender" (114)" is not valid.

Error at Data Flow Task [Deri

Convert String DateTime



A texbox which sets a date (MM/dd/yyyy) format and I want to add current time(HHmmss) to that date and assign this to a DateTime variable. At the end output format should be in yyyyMMddHHmmss and this should be in DateTime fied.

Is it possible? I tried as below but never succeeded. Could you please help me to resolve this!!


DateTime date = Convert.ToDateTime(TextBox1.Text);


TimeSpan time = new TimeSpan(23, 50, 0);

date = date.Add(time);


dropdown list fill with Linq and convert string to datetime and ordering and stored procedure call.


I wanted to share this more than anything,  it took me most of the morning searching and finding various post on various sites but I got it figured out and working.  It might be better coded, but it is a start for some one trying to understand how linq and ASP.Net things work.

What I have is a database table with a datetime in string format and a stored procedure which returns all the data.
What I want to do is load a dropdown list with MonthName and Year for a selection choice on generating monthly report.

The stored procedure is in a Linq to SQL class dbml and the connection string is dynamic, i.e., made througha call to another class.

here is the code.  Enjoy understanding how it works.

// Miscellaneous Details are filled in for helping you get the big picture.
// Some of the using statements are for other things in the code behind, 
// but I left it in so don't get confused,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections.ObjectModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using MYProject.LinqtoSQLStuff;

namespace MYProject.ChooseReportPage
	 public partial class TheReportPage : System.Web.UI.Page  
	     protected void Page_Load(object sender, EventArgs e)


Failed to convert parameter value from a String to a Guid.


I am trying to get the UserRole from the logged in user and send it to the database table my code is:

SqlParameter FamilyFriendsOther = new SqlParameter("@Role", SqlDbType.UniqueIdentifier);
        string [] roles = System.Web.Security.Roles.GetRolesForUser();
        if (roles.Length > 0)
            FamilyFriendsOther.Value = roles[0];

How Convert Datetime (YYYY/MM/dd HH:mm:ss) To yyyy-MM-dd



How do you write a date value to an XMLElement in .NET 3.5 XMLDocument object. 


 I am using the following:


DateTime orderDate = new DateTime();
orderDate = DateTime.Now; // 2010-08-24 16:45:46

string st = XmlConvert.ToString(orderDate, "yyyy-MM-dd");     // st="2010-08-24"
DateTime dt2 = XmlConvert.ToDateTime (st, "yyyy-MM-dd");   // dt2 = "2010-08-24 00:00:00"

I would like to remove the time section "00:00:00". I only need to write a date in the XML file with the following format :


Order date is defined like this in the xsd file : <xs:element name="OrderDate" type="xs:date"/>


Thanks for your support.

convert english string to hindi string

Code to convert english to hindi string

Data Conversion - string to datetime help!!!

Hi all, I need help on concatenate two fields into one then display it as datetime format as such yyyy-mm-dd hh:mm:ss AM/PM. The db is on SQL 2008 SP1. The date and time columns are on the same table and store as varchar. Date has varchar(10) and time has varchar(16). Date column shows result from query is 20100827 while the time column is either 070101.000000 or 070101. The question is how go about concatenate the two together and display in the format above in a report? How should I write my select query for this report? I am using SQL 2008 R2 BI Development Studio to design my report. Thank you. VCHS

Convert XML DateTime to SQL DateTime

I have tried to convert XML datetime value to SQL datetime by executing the below statement, though I feel there might be some other way of converting the value in SQL server 2000. declare @TestXMLTime as varchar(50) set @TestXMLTime ='2010-01-01T00:00:07+05:30' select cast(left(@TestXMLTime,10)+' '+right(left(@TestXMLTime,19),8) as datetime)    

Convertion failed when converting datetime to String

DECLARE @usr datetime SET @usr = user+'::Date' IF NOT EXISTS (SELECT 'X' FROM table WHERE Name = 'File' AND CONVERT(varchar,DateAdd, 101) = CONVERT(varchar, getdate(), 101)) BEGIN INSERT INTO table (Code, Name, Date) VALUES ('DD', 'File',@usr) END the date will be created by another process and its value will be loaded in the variable DATE. I'm calling that variable here. I;m getting the error Convertion failed when converting datetime to String. how to resolve this..??

Convertion failed when converting datetime to String

DECLARE @usr datetime SET @usr = user+'::Date' IF NOT EXISTS (SELECT 'X' FROM table WHERE Name = 'File' AND CONVERT(varchar,DateAdd, 101) = CONVERT(varchar, getdate(), 101)) BEGIN INSERT INTO table (Code, Name, Date) VALUES ('DD', 'File',@usr) END I;m getting the error Convertion failed when converting datetime to String. how to resolve this..??

Convert Q&A database (symantec) to SSIS supported database?

Hi Guys, What are the possible ways of converting Q&A  database (symantec) to SSIS supported database? Any clue will be of great help~Technology Analyst~

Failed To Convert Parameter Value From A String To A Int32

 This is my stored procedure: PROCEDURE[dbo].[uspEXPENDITURE_DETAILS] ( @ExpDetailsID int=0 output, @ExpDet_ExpID int, @ExpDet_Municipality_ID nvarchar(50), @ExpDetGeneralFund money, @ExpDetSpecialRevenue money, @ExpDetCapitalProjects money, @ExpDetTotal money, @Exp_Reporting_Year varchar(10), @ExpComments varchar(max), @Contact_ID int ) AS BEGIN  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON; deletefrom [dbo].[TBL_EXPENDITURE_DETAILS] where ExpDet_ExpID=@ExpDet_ExpID and ExpDet_Municipality_ID=@ExpDet_Municipality_ID and Exp_Reporting_Year=@Exp_Reporting_Year If@ExpDetailsID >= 0 Begin INSERT     INTO [dbo].[TBL_EXPENDITURE_DETAILS] ([ExpDet_ExpID] ,[ExpDet_Municipality_ID] ,[ExpDetGeneralFund] ,[ExpDetSpecialRevenue] ,[ExpDetCapitalProjects] ,[ExpDetTotal] ,[Exp_Reporting_Year] ,[ExpComments] ,[ExpDetCreatedBy] ,[ExpDetCreateDate]) VALUES  (@ExpDet_ExpID , @ExpDet_Municipality_ID , @ExpDetGeneralFund, @ExpDetSpecialRevenue , @ExpDetCapitalProjects, @ExpDetTotal, @Exp_Reporting_Year, @ExpComments, @Contact_ID, GetDate () )    SET @ExpDetailsID = SCOPE_IDENTITY()  return @ExpDetailsID end   Public Sub CreateExpenseDetails() Dim item As GridViewRow Dim txtExpGenFund, txtExpD

Convert float to String

HI, I am creating a derived column in my workflow. This derived column would have a concatenated value or Currency + [Account No]. Derived column is of type DT_STR. Currency is string (which is fine). I am having trouble converting [Account No] from float to string I tried Currency + (DT_WSTR)[Account No] and Currency + (DT_STR)[Account No] I am a newbie here... so, excuse my stupid question

Varchar string to int - how to convert empty cells to nulls

Hi, I have a large table from which I want to convert certain columns from varchar to int while loading the table to another table. However, I run into problems when the input column contains empty cells. The SSIS data conversion data flow item can't handle these so I tried the script component. With the script component I can convert these empty cells to zeros but after that I'd need to use SQL update to update the zeros to nulls which in the case of a large table with over 3 million rows takes unnecessarily long. So, is there another way?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend