Convert to datetime from varchar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert to datetime from varchar

Author

Topic

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2002-10-10 : 21:14:55

Hey All..
On a daily basis I'm receiving a 130+ column 300K row flat file feed from another source and importing it locally into a SQL 7 server.

The problem I'm running into is that the date fields are presented as varchar(8) in the format MMDDYYYY.

Using Bulk Insert, I was unable to import the values as a datetime datatype, so I've resorted to varchar for the time being. (If anyone can help me out to import it as datetime up front, that would be great too)

What I'm trying to do now is create a view and convert the date strings to a datetime datatype so I can do some calculations with it. Unfortunately, I'm obviously doing something wrong. Here's the t-sql I'm using:

CONVERT(DATETIME, LEFT(HIREDATE, 2)
+ '/' + SUBSTRING(HIREDATE, 3, 1)
+ '/' + RIGHT(HIREDATE, 4))

I've also tried simply converting the hiredate variable to datetime.

Either way, I get a "out of range datetime value" error.

I'm at a loss. If anyone can offer some direction I'd appreciate it.

Thanks,
Bob

SamC
White Water Yakist

3467 Posts

Posted - 2002-10-10 : 22:32:37

I may be missing something.

Have you tried

CAST (HIREDATE as datetime)

Sam

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2002-10-11 : 01:03:53

Sam,
I've tried that too, just forgot to mention it -- sorry! :). Here's the exact error I get when I do:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

For the error above, I ran the following query:

select ssn, last_name, first_name, cast(hiredate as datetime)
from tbl_employees
where ssn = '123-45-6789'

Note: Hiredate would be something like '04151994' (MMDDYYYY).

Thanks,
Bob

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-11 : 03:29:15

This should do it :

select Convert (DATETIME, Right ('04151994', 4) + Substring('04151994', 1, 4), 112)

it will return the date in ISO format :
1994-04-15

HTH

-ashok
http://www.unganisha.org

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2002-10-11 : 16:05:43

Thanks Ashok!

That worked perfectly :)

Bob

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2002-10-11 : 18:48:03

Any suggestions as to how I could go about converting the data during the import instead of after the fact?

Bob

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-11 : 19:59:54

You can try using an ActiveX transform instead of a regular column transformation, but the performance will go WAAAAAAAAAAY down. It will be much faster to import into a holding/staging table and do the conversion while INSERTing into the final destination table.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-11 : 20:46:31

Would it be possible to parse the file and rewrite it before it gets imported?

oitsubob
Yak Posting Veteran

70 Posts

Posted - 2002-10-12 : 02:25:09

I've thought about doing that, but the file is pretty large. Last I checked it was about 35MB.

I'll probably end up taking the route that Rob suggested, but I was hoping there might be another way.

Thanks for everyone's help! If you have any other idea's let me know..

Bob

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-12 : 04:57:15

quote:

Would it be possible to parse the file and rewrite it before it gets imported?

that is not a bad idea, if you are familiar with windows script (vbscript/javascript) or perl you can write a script to convert your 35 mb file (you'll probably need to use a bit of regular expressions).
take a look at http://www.win32scripting.com to get you started...

-ashok
http://www.unganisha.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources