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


Post New Web Links

Importing text file datetime string into SQL datetime column

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server
 

Hello,

I'm importing a text file into SQL Server 2008 R2 using SSIS 2008 R2.  Included in the text file is a datetime column with values like "2010-09-24 18:58:29.220".  However, when I import the file, the stored value has the time portion replaced with what I assume is the default, such as "2010-09-24 00:00:00.000".

Is there an easy way to import a datetime value from a text file into a datetime column in my table?  I've tried every form of date datatype in the Flat File Connection Manager, but I still keep getting the correct date portion with the incorrect time portion.

Am I trying to do something that wasn't intended?  I see Todd McDermid's page (http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html) and am starting to wonder if perhaps I'm trying to shortcut something that I shouldn't.

Thanks,
Eric




View Complete Post


More Related Resource Links

Importing text file datetime string into SQL datetime column

  

Hi,

Using SSIS 2008 R2 to import text files.  Two of the fields contained are datetime values in the format "2010-09-24 18:58:29.220".  Ultimately both the date and time need to be extracted with precision to the second, at the very least.  When I import the text file, however, the time value seems to get truncated before the date value's being imported, for an imported result of "2010-09-24 00:00:00.000".

Is my problem with the Flat File Connection Manager?  Regardless of which DataType choice I use for the properties of my datetime values--I've tried every form of date datatype--the imported column values contain the correct date but the wrong time value.

Is there an easy way to import a text file datetime value straight into a datetime column?  Is it possible, or am I trying to shortcut what should be a proper process?

Thanks,
Eric


I am having trouble importing a datetime from a text file.

  

In my Derived Column Transform editor I have the following

(DT_DBTIMESTAMP)(SUBSTRING(TRIM(Admit_Date),1,4) + "-" + SUBSTRING(TRIM(Admit_Date),6,2) + "-" + SUBSTRING(TRIM(Admit_Date),9,2) + " " + SUBSTRING(TRIM(Admit_Date),11,2) + ":" + SUBSTRING(TRIM(Admit_Date),14,2) + ":00")

The data type column has string [DT_STR] as the value. 

The string looks like 2011-04-04 23:00 

The connection manager has the datatype set to string [DT_STR) with the OutputColumnWidth set to 18

The Admit_Date field is in a temp table created like this [ADMIT_DATE] datetime NULL, 

And below is the error I get

[Derived Column [203]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (203)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "Admit_Date" (251)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

Any ideas. Everything I have read tells me to parse the string out like I have, and it works for all my date only fields. e.g. (DT_DBDAT

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

Datetime column with [Computed column specification] formula

  
i have a datetime field in my SQL Server table i dont want to add weekend dates in this column. For this reason i always have to check the calendar. how can i use [Computed column specification] formula so that i never add weekend dates?

How to read a column of a sharepoint list to a text string field of infopath form?

  
My infopath form used to read these email addresses from a web service and web.config file. Now i would like to get these user email addresses from a contact list in a sharepoint server. I am trying to do this without writing any codes. Here is what i have done so far: 1. Adding a new "data connection to receive data from SharePoint library or List" where i selected Email Address column of the contact list. 2. I added a new Text Field and define a new rule with "Query using a data connection" defined above. When i try to access the newly created Text field above it always return empty as if the Query attached to it did not executed at all. What did i do wrong? Is there a better way? Thank you in advance for your help.

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..??

Unable to add filter to a sharepoint DateTime Column

  
Hi ,I have a DateTime column in  a List. When I try to create a view the column doesnt appear in the folter Column List.When i create a calculated column out of the DateTime Column, the new calculated column appears in the Filter Column ListCan any one explain why?ThanksSrila

Flat file upload: Error when inserting into datetime field.

  
Hi All I am having an issue with uploading a txt file into an SQL table and one of the fields needs to go into a datetime column. The issue is with column3 I have been into the advanced editor of the flat file source and set the output column to decimal and it fails. The error I am getting is below: Error: 0xC02020A1 at Materials Transfer, Flat File Source [1]: Data conversion failed. The data conversion for column "Column 3" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". Error: 0xC0209029 at Materials Transfer, Flat File Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "Column 3" (48)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 3" (48)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure. Error: 0xC0202092 at Materials Transfer, Flat File Source [1]: An error occurred while processing file "D:\Development\r3_downloads\mat.txt" on data row 1. Any help would be great.   Thanks BigGopher

How to redirect bad date(xxx,9999) in datereceived column in text file in ssis

  
I want to redirect bad date format from flate file source to log table. I tried with redirect row but it is not working. Thanks in advance

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.

Converting string to DateTime

  
Hello, I am trying to figure out the best way to convert a string object into a DateTime with only using the year.I have something like:Date Time HSYear = DateTime.ParseExact(HighSchoolyear, "yyyy", System.Globalization.CultureInfo.CurrentCulture); But it is still giving a "String was not a recognized as a valid DateTime."In the full context, I have a webpage with a formview on it and within the formview is a TextBox control that is taking a user input such as "1999"  then an ObjectDataSource picks it up and runs it through a BLL. And within the BLL is where I am trying to convert it before it gets inserted into the database.

String was not recognized as a valid DateTime.

  
Hai friends,            I have used following code                  newClient.Dob = DateTime.Parse (textBoxDOB.Text.Trim());I convert to string also..But  It Shows  error in Run Time Please verify it,                 Error :  String was not recognized as a valid DateTime.

converting string to datetime and then to shortdate?/

  
Hi,I have a field (varchar) field that holds date.  Some data was transferred to this column but the transferred data was a timestamp (datetime in the format of say 10/26/2009 4:54:13 AM).  Now, I need to modify the new table to only contain 10/26/2009.  How can I write a statement to delete the time leavin gonly 10/26/2009??  I can use trim function but the # of characters vary as the date might be 1/10/2010 12:50:00 PM, etc....?thanks .

how to insert null or empty string to datetime var ?

  
hi i get data from xml file and sometime the date is empty. i have this code:     try { TimeTo = Convert.ToDateTime(R[15].ToString()); }         catch { TimeTo = null ; } but i got error because i cant insert null to datetime var what i can do ? thak's in advance

how to retrieve various date format from datetime datatype column

  

first table

Area_code (varchar)    Loan_date (datetime)

USA                             9/21/2009  12:00:00 AM

CAN                            10/22/2010  12:00:00 AM

ARG                            12/23/2009 12:00:00 AM

Second table

Area_code (varchar)    Date_format(varchar)

USA                             dd/mm/yy

CAN                             yy.d.m

ARG                            yy'?'d'?'m'?'

I want to add Loan_date column in second t

how to retrieve various date format from datetime datatype column

  

first table

Area_code (varchar)    Loan_date (datetime)

USA                             9/21/2009  12:00:00 AM

CAN                            10/22/2010  12:00:00 AM

ARG                            12/23/2009 12:00:00 AM

Second table

Area_code (varchar)    Date_format(varchar)

USA                             dd/mm/yy

CAN                             yy.d.m

ARG                            yy'?'d'?'m'?'

I want to add Loan_date column in second table and nee

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