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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

convert datetime to Numeric in ssis

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :Sql Server
 
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


View Complete Post


More Related Resource Links

Convert SSIS DateTime to a String

  

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.

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

  

Hello,

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 :

<OrderDate>2010-08-24</OrderDate>

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

 

Thanks for your support.


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)    

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~

Importing Data from Excel into SQL Server using SSIS: some datetime values appear as NULLS How to Re

  
I created a Package in the Business Intelligence studio to Import data from Excel file  into SQL Server 2005 using a Excel Source and a OLE DB Destination that uses a data convertion transformation before it reaches the destination a mjority of the data is copied over. However i am having 2 Issues. 1. In the Date field some of the values appear as Null in SQL Server 2. I need to change the format of the date in Excel from dd/mm/yyyy to mm/dd/yyyy before inserting into SQL Server if Possible. I am not sure of the solution for Ques 1 but i attempted using a script task for #2 It did not work. Please Advice what the best way to proceed Thanks.

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);
mySqlCommand.Connection.Open();

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 Text to Numeric in reports generated by Microsoft SQL Server 2000

  
Our programming team is producing Excel reports using Microsoft SQL Server 2000 and cannot seem to find a way to produce numeric data as numeric.  It is being produced in the spread sheet as text (with the green triangle in the corner of the cell).  If the user viewing the report has an older version of Excel they cannot even convert it to numeric manually. 

Is there a way to produce numeric data as numeric in the Excel report?

SQL Server DateTime Convert pattern

  

I need the datetime to be in the following format for SQL server 2005

dd/MM/YYYY HH:MI in 24 Hour Format

note: seconds are not included

 

Thanks for any help in advance


How to Convert datetime to hour

  

int s = 0;

        //Start time
        int start_year =2010;
        int start_month = 10;
        int start_day = 2;
        int start_hour = 0;
        int start_min = 0;

        //End time
        int end_year = 2010;
        int end_month = 10;
        int end_day = 3;
        int end_hour = 0;
        int end_min = 0;
        
        DateTime startTime = new DateTime(start_year,start_month,start_day, start_hour, start_min,s); // 05:40 Hour
        DateTime endTime = new DateTime(end_year, end_month, end_day, end_hour, end_min, s); // 17:45 Hour
        TimeSpan diff = endTime - startTime;
        int hourDiff = diff.Hours; // 12 Hours
        int minDiff = diff.Minutes; // 15 Mins
        double totDiffHours = (double)hourDiff + (double)minDiff /60; // 12.25 Hours
       // double hhh = (DateTime) diff / 24;

        Label1.Text = Convert.ToString(diff);

How to convert days into hours?

Thanks


DateTime Conversion Issues in SSIS

  

Hi,

I have a Weird issue with SSIS. I have some input data of dates like '01/01/1999' and '01/01/0207. First one is a valid date and later is invalid. I have to use DataConversion inorder to convert it to datetime and re-direct the errored row to Flat-file destination.

Now the Issue is:

If I convert to DateTime (DateTimeStamp) in Data Conversion, it is treating both the dates as Valid and failing in my SQL Destination as the later is an invalid date.

If I convert to DateTime2 (DateTimeStamp2 with Precesion) in Data Conversion, it is treating both the dates as Invalid?

How do i fix this in SSIS (In a simple way) to throw 1 as error and other as Valid? Any help is appreciated.

Thanks,
Srini...

 


Convert NVARCHAR column to DateTime - SQL Server

  

Hi there,

Have spent few hours to get this working. I like to get help now. Please note MONTH AND DATES stored in single or double digits as they occur.

Unfortunately DATE TIME is stored in NVARCHAR Data type column as 2/9/2010,  22/10/2010 etc. Following UK Date here.

It is required to pull the data to COUNT No. of Expired. Obviously below one fails.

--AS Date AND Month stored in Single digits AND double digits , below statement 
--thorws Arthmetic overflow exception
SUM(CASE WHEN CONVERT(DATETIME,ExpiryDate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND    
 CONVERT(DATETIME,ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired  


 


Convert NVARCHAR column data into DATETIME - SQL Server 2000

  

ExpiryDate column in Table Data Type is NVARCHAR , storing Dates ONLY without time as 2/10/2010 , 12/10/2010 IN DD/MM/YYYY FORMAT.

NOW IT IS REQUIERD TO RETRIEVE ALL RECORDS WHERE EXPIRYDATE BETWEEN USER PASSED START MONTH,START YEAR AND END MONTH, END YEAR.

USING SQL SERVER 2000

SUM(CASE WHEN CONVERT(datetime,a.expirydate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND 
		CONVERT(DATETIME,a.ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired

 

Your help is greatly appreciated. Spent hours to get this resolved.


A

Convert NVarchar to DateTime - SQL Server 2000

  

Hi there,

FROM USER INTERFACE END USER SELECTS START MONTH, START YEAR AND END MONTH, END YEAR. IT IS REQUIRED TO RETRIEVE DATA WHERE ONE OF THE FIELDS I.E, ExpiryDate is of type NVARCHAR, stored dates as 2/10/2010 [DD/MM/YYYY] , 10/10/2010 ETC.

Using SQL Server 2000.

ALTER PROCEDURE [dbo].[CountUsers]
		(
			@startMonth nvarchar(20) =	NULL,
			@startYear nvarchar(20) = NULL,
			@endMonth  nvarchar(20) = NULL,
			@endYear  nvarchar(20) = NULL,
			@startDate nvarchar(50) = NULL,
			@endDate  nvarchar(50) = NULL
		)
		AS BEGIN

		SET @startDate = @startYear + '-01-' + @startMonth; 
		SET @endDate  = @endYear + '-01-' + 

SSIS excel data source numeric values returned as null

  
Hello everyone, long time no see!, I have a problem and was hoping someone can help me:
I'm using SSIS 2005 Enterprise edition,  I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server).
When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.

The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it.

I tried the answer posted here: http://devselekta.blogspot.com/2007/09/ssis-excel-data-source-values-returned.html but it only works when the first row of data has a number beginning with zero on this column.
Someone knows how to get the data imported correctly? Thanks in advance.
Odin_Dark

How to convert CSV with % with SSIS

  

Hello all the experience SSIS user out there,

 

Here's the  code for my source file csv formatted:

"ID","Video Name","Views","Viewed Minutes","Percentage to 50% Mark","Percentage to 75% Mark","Percentage of Complete Views"
"71579527001","Royal Distributing Inc","164","72","84%","75%","73%"
"79545950001","Session de formation Montréal - Nouveaux produits","150","227","65%","60%","42%"
"71597962001","Alibi Coiffure","139","56","80%","68%","65%"
"71605945001","Attache De Remorque Québec","127","99","71%","64%","62%"
"71593233001","Maison Victoria (La)","117","99","80%","70%","58%"
"71586694001","La Faune Domestique","117","47","76%","64%","64%"
"71603800001","Ameublement Lida Furniture Inc","112","54","82%","76%","74%"
"71585732001",&

DateTime Validation in SSIS using conditional split

  

Hi,

I am facing issue with null validation for DateTime. I have input csv file which contains DateTime field as Date datatype. In the destination database table, I am storing this field as datetime. I am using conditional split feature of SSIS package and try to validate this field before inserting into the database.

The value which is going into the db is "1899-12-30 00:00:00.000". I dont know why?

I have tried various expressions like ISNULL(DateTime), LEN(DTSTR, DateTime) == 0, ISNULL(DateTime) ? False : DateTime. Nothing is working.

Req is simple, if date is valid insert in db otherwise write is as bad data in error log file.

Can anyone provide suggestion on this.

Thanks,

SandyLeo


SandyLeo

convert datetime to varchar and again to datetime

  

Hi all,

I have a datetime column with value 2010-06-23 03:30:00.000

I need to convert this to varchar.after converting to varchar then again i need to convert the output to datetime2(3).

How can i do this.

thanks in advance!!


Categories: 
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