Flat File Text Date Conversion To SQL Server Date Comments And Suggestions

Mar 12, 2008

Hi,
Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -

In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".

1) File Connections Manager Editor
1.1) Within File Connections Manager Editor; -
Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC"
and assign a type to the data type e.g. string[DT_STR]

1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.


2) Derived Column Transformation Editor
2.1) Assign Derived Column Name, e.g.
INTERCHANGE_NET_APP_DATE

2.2) Select <add as new column> within Derived Column.

2.3) Enter the conversion Expression, e.g. ; -
2.3.1)
(SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,8,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,5,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,1,4))

2.3.2)
Since the above conversion is such a common task I suggest that Service Pack 3 of SQL Server 2005 delivers the following functionality; -

STRINGTODATE ('YYYYMMDD',INTERCHANGE_NET_APP_DATE_SRC)

2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.

2.5) Within the Mappings tab of the SQL Destination Editor have; -
Input Column as INTERCHANGE_NET_APP_DATE and
Destination Column as INTERCHANGE_NET_APP_DATE.

Please comment on the above, I will then pass on my suggestion to Microsoft.

Thanks in advance,

Kieran.

View 1 Replies


ADVERTISEMENT

Import Flat Text File String Date To Database Date

Jan 30, 2007

I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose

View 1 Replies View Related

Date Conversion - Flat File - YYYYMMDD

Jul 19, 2007

Hi,

What is the new way to transform flat file dates into SQL datetime datatype. Being average user in SQL 2000 DTS I would simply use "Date Time String Transformation Properties" and transform the date into the format I need, in SSIS I haven't found an elegant way of doing this.

My thoughts are to use €œdata conversion€? utilizing substring expressions€¦

Thanks the help
Bill

View 6 Replies View Related

String/Date Concatenation Causes Conversion Error - Streamline Fix Suggestions

Sep 4, 2007

Hi,I'm trying to concatenate a Description (nchar(100)) and Date(datetime) as Description and my initial effort was just"...description+' '+open_date as description..." which throws a date/string conversion error; finally came up with a working string belowbut don't think it's the optimal way to do this - any suggestions?select (rtrim(description)+''+rtrim(convert(char(2),datepart(mm,open_date)))+'/'+convert(char(2),datepart(dd,open_date))+'/'+convert(char(4),datepart(yyyy,open_date))) as description fromoncd_opportunity where opportunity_id=?open_date is not a required field at the db level, but it is requiredon the form so it should not be null as a rule.

View 2 Replies View Related

Text To Date Conversion

Dec 13, 2000

I have a very large table with about 15 "date" fields that were imported as text from a flatfile. I need to convert these fields to dates. The format that they are currently in are like this "12/31/99".

What is the easiest way to convert these fields to dates?

Thanks for any help
Jason Fitch

View 1 Replies View Related

Text-to-Date Conversion

Jun 3, 2004

How can I convert this string into the correct SmallDateTime type.

STRING = "060104" [This is JUNE 1, 2004]. When I use a CAST, I get [JANUARY 4, 2004] instead.

TIF

View 2 Replies View Related

Exporting To Flat File With Date Only

Nov 28, 2007

I am trying to setup ssis data flow package to export a smalldatetime field to just date only. I have changed the dattype to datbase date [DT_DBDATE] AND ALSO [DT_DATE] but it still exports datetime format. What is the best and easiest way to setup flat file connection manager to only export date and not the time. Do I have to add another setup to convert to date only?

View 1 Replies View Related

Date Output To Flat File

Jul 18, 2007

I am using a simple input from an SQL data base where I have 4 dates defined as type D. I am writing to a flat file with the fileds defined with any available date format and the output on the flat file comes out as "mm/dd/yy 00:00". I'd like to just have the date portion with no time. The input does not have a time on it so I understand the 00:00 as the value. It seems that I shouldn't have to do any extra work as it is date to date. I've seen the gyrations for a date from the SQL database when it is a character field, but that's not the issue here.

Thanks!

View 8 Replies View Related

Date Being Discarded In Flat File Import

Dec 21, 2007

I am importing from a flat file source that is pipe delimited. I have a few files that all follow the same format but now that I am dealing with subsequent files, they are reacting differently and the date is being discarded. I have opened the subsequent file into Excel without any column conversion issues. But whenever I run the package, about halfway through the dates become NULL. I have defined the field as DATE, DatabaseTimestamp, String (convert to Date using substring parsing method) and all of these yield the same results. Any ideas?

View 1 Replies View Related

Date Issues While Importing From Flat File

Nov 5, 2007

I have a Data loading job using SSIS (Flat File to SSIS).

I been having issues with some bad data records while importing the file.
(Date Column).
Here is an example -- 7/31/0200 (Actually have around 600 Records of this nature)

While importing these records, the package fails as it cannot convert this "7/31/0200" string to a DATTIME Column Value.

Any idea on how i can overcome this?

View 1 Replies View Related

Date Function - Conversion Failed When Converting Date And / Or Time From Character String

Mar 18, 2014

I have the following

Column Name : [Converted Date]
Data Type : varchar(50)

When I try and do month around the [Converted Date] I get the following error message

“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.â€

My Query is

SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]

View 7 Replies View Related

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

SELECT * ,[Due]
  FROM [Events]
 Where Due >= getdate() +90

This returns the error: Conversion failed when converting date and/or time from character string

Why would this be? How to cast or convert this so that it will work? 

View 24 Replies View Related

Date Formats From OLE DB Source (SQL) To Flat File Destination

Oct 23, 2007



I am bring a date from a OLE DB Source (SQL Command) as [select cast(convert(varchar,getdate(),101) as varchar(10))] to a Flat File Destination (CSV File). The data in the source is show as "1/1/2007", which is how I need it to display in the file. The flat file defaults to showing the data as "1/1/2007 00:00:00." I did change the destination field to a String, and still, I geting the timestamp. I tried using a data conversion transformation, and I am getting bargage data when converting the date to a string.

Can any one give me insight on how to populate a date into a comma delimeted file as "1/1/2007", not "1/1/2007 00:00:00."


Thanks in advanced.

View 8 Replies View Related

Importing Date Values From A Flat File Into The Database

Aug 7, 2007

Hi

I am trying to to import a flat file into a table in my database, i get all the values right except for the date, it keeps on inserting NULL values into the date fields.

The date format in the flat file is '20070708' etc.

Does anyone know what i can do to fix this?


I've tried to change the datatype values that it imports, but it still ignores it and inserts NULL values

Any help will be greatly appreciated

Kind Regards
Carel Greaves

View 3 Replies View Related

Conversion Of Oracle Date Time To Sql Server Date Time In SSIS

Jun 30, 2007

This is driving me nuts..



I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.



I'm struggling with this for a quite a while and I'm not able to get it working.



I tried the oracle query something like this,

SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE

FROM TBLA

this gives me an output of 20070511 23:06:30:000



the space in MM : SS is intentional here, since without that space it appread as smiley



I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error

The value could not be converted because of a potential loss of data



I'm struck with error for hours now. Any pointers would be helpful.



Thanks

View 3 Replies View Related

Use Script To Read Date From Specific Line Of Flat File

May 10, 2007

I have a few flat files that I need to read a date from. The date will always be on Line No 4 and will be in YYYYMMDD string format in chars 2-9 of line 4



Here's what I have so far to convert the string YYYYMMDD into date format... I don't know enough VB to be able to just hit Line 4... I am sure it's simple so could someone pelase put me out of my googling misery






Code Snippet

Public Sub Main()

'

Dim oStream As New IO.StreamReader("Z:TreasuryFilesdeals.dat")

Dim strReadLine As String, filedate As Date



'??? how to just read a date from the fourth line of the file ??

'strReadLine = oStream.ReadLine()

strReadLine = "A20050331" 'sample string variable

'convert the fourth line of the flat file to a date format

filedate = New DateTime( _

CInt(Strings.Mid(strReadLine, 2, 4)), _

CInt(Strings.Mid(strReadLine, 6, 2)), _

CInt(Strings.Mid(strReadLine, 8, 2)))

'write filedate to package variable

Dts.Variables.Item("User::Load_Date_Loan").Value = filedate

System.Windows.Forms.MessageBox.Show(CStr(Dts.Variables.Item("User::Load_Date_Loan").Value))

'

Dts.TaskResult = Dts.Results.Success

End Sub

View 4 Replies View Related

Date Time Format Options When Writing To A Flat File

Apr 24, 2006

We are using an ADO.NET provider in SSIS to read data from a SQL Server 2000 table that contains DateTime columns to write to a Flat File Destination. When the date values are written to the file they are formatted in TimeStamp to the 10th decimal position; e.g.€œ2006-04-24 12:00:00.123000000€?. Since SQL Server supports values to Timestamp(3), we need to truncate the last seven zeros to put the data in this format €œ2006-04-24 12:00:00.123€? to keep the file as small as possible.

Since we have several hundred DateTime columns in scope for our requirements we are looking for the least logic/effort to accomplish this task. We can do this via Data Conversion and Derived Column transformations to cast the dates and strings but it is very labor intensive. It would be something like singing 99 bottles of beer on the wall eight times in a row with each verse taking 3 minutes each. Yikes.

We have tried casting the DateTime columns to varchar in the SELECT statement but receive this format €œApr 24 2006 12:22PM€?.

Is there a configuration we've missed that forces timestamp(10) with non significant digits?

View 1 Replies View Related

Importing DATE With Timestamp(In A Flat File) Column Using SSIS

Apr 18, 2007

Hi

SSIS is brand new for me.. Playing with since a few hours..

Iam trying to import a Flat File into the SQLSERV DB using SSIS..
One of the column is in this format -- "YYYYMMDDHH24MISS"

How do i get around this to import the data in a readable fashion into the Destination?

Thanks!
MKR

View 6 Replies View Related

Integration Services :: BCP Format File Date Type Conversion?

Sep 11, 2015

I've a text file which having a datetime column value like YYYY-MM-DD-HH.MM.SS.XXXXXX. I cannot convert this is to datetime format from text file using BCP utility. 

Presence of hypen "-" between DD and HH, the SQL server does not accept this is as Datetime.

Is there any option to covert the date value in format file in BCP.

View 3 Replies View Related

Flat File -&&> Table: Error Using I/E Wiz, Date..could Not Be Converted..potential Loss Of Data

Jul 16, 2007



The following error is encountered when importing a delimited flat file with date of fomat "dd.mm.yyyy"



Error: 0xC02020A1 at Data Flow Task, Source - DCDtest_xpt [1]: Data conversion failed. The data conversion for column "value date" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".



This was when I manually built the package.

I get the same error when using the Import/Export wizard



I am even using the "suggest types" button and have tried sampling the default number of rows (?200) and also 2000.



The type it suggests is DT_DATE.

But reading the BOL, this would appear to be the wrong type:

http://msdn2.microsoft.com/en-us/library/ms141036.aspx (obviously the right hand doesn't know what the left hand is doing)



seems to indicate that

DT_DBTIMESTAMP

is the correct value

(I cannot believe that they have different datatypes in SSIS than in SQL. I can't believe for a minute this is for all the hundreds of thousands of Oracle users who obviously switched to SSIS when they saw what a high quality product it is.)



I tried other DT_... values but no dice.



Can anyone help?





I always thought that Classic ASP was the worst product I've ever worked with from the Microsoft stable, but I was wrong.

I am fed up of having to post on this board (no wonder it is so 'popular')

Talking to peers, reading books, googling nearly always enables me to figure out a problem with any application I have ever used, but SSIS breaks the mould in sheer crapness and the weirdnes and unfathomability of its cryptic errors,.

Rant over (for today)

View 9 Replies View Related

Management Studio - Script Date Comments

Aug 22, 2007

Hello,

This may sound like a stupid question, but I have looked a number of times where the options are for the comments that are added to a SQL Script that is updated or newly created.

Ex this is the line that is added:


/****** Object: StoredProcedure [dbo].[Rpts_SelectDesignLumberInv] Script Date: 08/21/2007 19:42:50 ******/

I would like to be able to add the developer name, date created etc to the default comment that is added by the designer.. So more or less I would like to be able to define what is commented.

Does anyone know how to do this?

Thanks, Leo

View 3 Replies View Related

SQL Server Date Conversion

Sep 30, 1998

Any ideas on converting integer to a proper date format in SQL server.

View 2 Replies View Related

Date Conversion In SQL Server

May 17, 2007

Hi folks,

Here are the fields I have

eventDate = smallDateTime 2005-12-12 00:00:00
eventTime = varchar(20) 1:00:00
newEventDate = dateTime [desired result: 2005-12-12 1:00:00]

When I run the following script:
update healthEvent
set newEventDate = cast(substring(convert(varchar,eventDate,120),1,10 )+' '+eventTime as DateTime)

I get "Syntax error converting datetime from character string." error.

Any ideas why?

Thanks!

-Parul

View 4 Replies View Related

SQL 2012 :: UK Date In Text Field As True Date

Jul 14, 2014

I have a date held in a varchar field in a temporary sql table and I want to convert it into a sql date and it doesn't work. I can replicate this as below -

So I run

select
cast ('14/02/2014' as date)

and I get a conversion failed error.

View 2 Replies View Related

Date Conversion In SQL Server 2005

Apr 25, 2008

Hello Everyone, thank you for taking the time to read my post. I'm creating a view in SQL Server 2005 to base a report on Crystal Reports XI. I've been trying to figure out how I can convert a date field in the format YYYYMMDD to MM/DD/YYYY. I'm not quite sure about the steps I need to take to accomplish this since I'm pretty new to this. The date is stored as an Int on the database, and I've tried converting it directly to the DATETIME data type like this: CONVERT(DATETIME, datefield,101) but I'm getting an error saying illegal data type conversion. Thanks a lot for your help, I really appreciate it.

MS

View 7 Replies View Related

Oracle To Sql Server Date Conversion

Mar 12, 2008

Hi Gurus,
I need to convert this statement to sql server.
SELECT to_date('24-08-2007 13:11:12','dd-mm-yyyy hh24:mi:ss'),to_date('24-aug-2007 13:11:12','dd-mon-yyyy hh24:mi:ss') from dual

can anyone help please.

View 1 Replies View Related

Need Suggestions On Text File Parsing Into Database

Feb 28, 2007

I have a website, where people upload tab delimited text files of their product inventories, which the site parses and inserts into a database table.  Here's the catch: Instead of insisting that each user use a standardized format, each user can upload the file in whatever column order they want, they just have to let the site know through a GUI which column is in which order.   And, they may upload columns that if not mapped, will be ignored.  Right now, I am doing all of this in code and it runs slow, I was thinking of offloading this to either a stored procedure, ssis, or bulk upload.   But, with the varying format of the uploaded text file, I am not sure how I could do that.  Any suggestions? Thanks! 

View 1 Replies View Related

SQL Server 2012 :: Serial Date Conversion

Nov 4, 2014

I am in need of converting serial date to regular date ie...735510.40461 and only need the hours, minutes and seconds, I have used the examples I've seen on different forums,

DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))

View 9 Replies View Related

SQL Server 2014 :: Clarion Date Conversion

Oct 7, 2015

The database for our software stores dates and times in clarion format. I'm trying to write some custom reports in T-SQL and I need to convert these dates but it's giving me a lot of trouble. How do I query the dates and times and have the results shown as a "regular" date/time? Below is what I have so far which is really the very beginnings of this report. Basically on the results/output I need the ClarionDate and ClarionTime to be shown as typical date/time columns. I did some research on my own but I'm having a hard time grasping this. I believe they have to be pulled into a temp table and then converted?

SELECT A1.DATEOFACCESS ClarionDate, A1.TIMEOFACCESS ClarionTime, A2.NAME Event
FROM dbo.History A1
JOIN dbo.SysEvents A2
ON A1.RSVD_Action = A2.RSVD_EVENTTYPE

View 2 Replies View Related

Using VB 6.0 And SQL Server: Date Causes Datetime Conversion Error

May 10, 2008

I'm using a VB 6.0 front end with a date variable
that throws a SQL 2005 datetime conversion error in the following UPDATE query:

UPDATE MYTable
SET Feild1 = 'VBVar1', Field2='VBVar2'
WHERE MydateField = 'VBdateVar'

I'm guessing that I probably am missing some appropriate method for passing the date datatype variable to SQL


Can anyone help me

Thanks

View 7 Replies View Related

Transact SQL :: Server Date Format Conversion

May 12, 2015

I am facing an issue

1:- From Source system ,I am getting dates like --- 

2014-Q3
2014-Q2
2014-09

As per my requirement, I need to convert this time to dates like 

1:- 2014-Q3  ---   last day of quarter 3 of 2014 .  
2: 2014 -02 -- last day of feb 2014

Converting such format  to the dates I expect...

View 8 Replies View Related

Transfering A Flat Text File In DTS To SQL Server

Dec 26, 2006

How do I tranfer a flat file (text) to SQL Server using DTS? Please point me in the right direction.

View 19 Replies View Related

SQL Server 2012 :: Conversion Failed When Converting Date

Oct 30, 2014

I have been trying to convert datetime but keep getting this error(Conversion failed when converting date and/or time from character string.It works just fine if I don't use execute sp_executesql,.

<code>
DECLARE @tablename AS nvarchar(max)
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @startdate datetime

[code]....

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved