Improve company productivity with a Business Account.Sign Up

x
?
Solved

import text datetime into SQL

Posted on 2012-12-28
8
Medium Priority
?
449 Views
Last Modified: 2012-12-29
How would I get the last column of the below text table into an SQL field that is a datetime field?  The first two columns are imported into varchar fields.  The last column needs to be converted into a datetime.


0016070|06|20141001|
0016071|06|20141001|
0016072|06|20141001|
0016073|06|20141001|
0016074|06|20141001|
0016075|06|20141001|
0016076|06|20141001|
0016077|06|20141001|
0016078|06|20141001|
001607B|06|20141001|
00160J0|06|20141001|
00160J1|06|20141001|
0
Comment
Question by:dastaub
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 668 total points
ID: 38728280
cast(thatpart as date)
will work, as the format yyyymmdd is implicitly ok.

see also this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 668 total points
ID: 38728281
convert(datetime, {value}, 112)

ref http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
LVL 12

Assisted Solution

by:sachitjain
sachitjain earned 664 total points
ID: 38728471
You may need to first import or bulk insert these records into some staging table with all varchar columns then you could do like this

begin transaction
      insert into ActualTable (Col1, Col2, Col3)
      select Col1, Col2, CONVERT(datetime, Col3, 112) from StagingTable
      delete from StagingTable
commit
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:dastaub
ID: 38728573
INSERT INTO orders1 ( order_date , ordering_client_id )
VALUES ( cast('20121215' as datetime), 2 )

The above works.  How do you conver this to import hundreds of row?
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 38728600
Depends how are you currently importing your hundreds of rows?
0
 

Author Comment

by:dastaub
ID: 38728609
i do not know how to import hundreds of rows
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 38728646
Your question only asks how to import the datetime component, you say "The first two columns are imported into varchar fields". which implies you are already importing the varchar data.

Your question as such has been answered - you should probably ask another question if you need to know how to go about doing an import. And you'll need to specify what format the data is in that you are trying to import e.g. is it just a text file with the data as you listed, and roughly how many rows are there?
0
 

Author Comment

by:dastaub
ID: 38729475
tried to sneak one in or keep the conversation going
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
Repair corrupted SQL Server database even if DBCC CHECKDB fails in SQL Server.  This blog describes, what to do when DBCC CHECKDB failed and printed database corruption errors.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question