converting datetime column to the text field

Question

Post reply

converting datetime column to the text field

jprabha.d

Ten Centuries

Points: 1086
More actions
February 2, 2010 at 8:45 am

#215219

Hi All,

I have excel file ,i extract a data from the this excel file and load it into the sql server table(Using ssis),

there is column called "date" in excel file,this column contains date as well as text say like "res in","res out",this is some text that is been entered in the date column ,

when i extract the data from the sheet and load it into the table ,my sql server table - date column shows up the "NULL" where ever the text as to be. i have changed th datatype of the date column as nvarchar for the table ,but still i get the same.

What can i do to get the text in my date column along with the dates.

plz help me out....

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11381 More actions · Answer 1

sounds like it's specific to the file. Can you provide a sample file as well as the table layout you're trying to import into?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

LutzM SSC Guru Points: 107049 More actions · Answer 2

My knowledge regarding SSIS is limited but I'd guess the mapping would still try to convert it to a date format...

I'm not sure if a stored SSIS package would recognize a data type change of the target table (as long as the data can be converted...).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Oksana March Mr or Mrs. 500 Points: 573 More actions · Answer 3

I have an idea...

Are your dates all numeric characters like 01-01-2010 ? Or are they like Jan-01-2010 ?

If they are all numbers, like 01-01-10 or 01-01-2010, since you said you already changed the date column to nvarchar, just filter out any non-numeric characters except the dash. Possibly move data in that column to a staging table first?

Not sure what is the exact code but it's WHILE loop with ASCII function. Maybe experts here can tell us 🙂

jprabha.d Ten Centuries Points: 1086 More actions · Answer 4

Hi,

Its Just an excel File :

Order No | Date

F000987 | 23/10/2009

F000101 |res in

JkP0091 |10/10/2009

when i import this 2 column in the sql server table i get like this

Order No Date

F000987 23/10/2009

F000101 NULL

JkP0091 10/10/2009

Instead of getting "Res in" i get a NULL values in the table,if it was just a one record i could have simply update the value ,but i have more than 10000 records,and based on some criteria my client make a changes in the excel file ,i just need to import the same values as it is in the excel file.

Thanks & regards

prabha