VarChar to SmallDateTime? - SQL Server Forums

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)

VarChar to SmallDateTime?

Author

Topic

TorreyKite
Starting Member

40 Posts

Posted - 2003-08-06 : 15:42:47

I'm having trouble converting varchar to smalldatetime

I'm doing a Bulk insert into my "TEMPDATA" table then converting each field as I put the records into my "TS_DATA" table.

I know I'm close but not quite there.


CREATE PROCEDURE sp_Insert_Final 
AS
INSERT TS_DATA (doctor, calldate, lastname, firstname, 
	address, city, state, zip, phoneh, phonew, tsr, appt_date, 
	appt_time, status, _rescode, showed, retried, confermer, priority)

SELECT substring(dr, 2, datalength(dr)-1), CONVERT(smalldatetime,calldate),
          ln, fn, addr, city, st, zip,
          phoneh, phonew, tsr, CONVERT(smalldatetime,appt_date), 
          CONVERT(datetime,appt_time), status, 
          CONVERT(smallint, _rescode), CONVERT(char, showed),
	  CONVERT(char,retried), confermer, 
          CONVERT(smallint,substring(priority, 1, datalength(priority)-1))
FROM TEMPDATA

error:
"Server: Msg 241, Level 16, State 1, Procedure sp_Insert_Final, Line 3
Syntax error converting datetime from character string."

Any Ideas?

thanks for checking it out

TK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-06 : 15:52:24

The stored procedure looks fine to me and it was successfully created on my server. You could have some hidden characters in there (other people have had this happen in Query Analyzer), so I would suggest copying it into notepad, then copying it back into Query Analyzer into another window. Then see what happens.

Tara

TorreyKite
Starting Member

40 Posts

Posted - 2003-08-06 : 17:11:33

hmmmnn. well I didn't have any trouble creating the Sp...

i got alittle farther but then hit this one


"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated"

all of my dates are in mm/dd/yy format ie: "08/06/03"
while i have a time that is: "15:00"
Do i need to set a special date format to do this?

then some of them are blank but still have the slashes " / / ",

can i use an IF THEN to exclude those?

Thanks for the help.

TK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-06 : 17:15:33

quote:
Originally posted by TorreyKite

then some of them are blank but still have the slashes " / / ",

can i use an IF THEN to exclude those?

Yes you will need to exclude them but you can do that in the SELECT statement:


CREATE PROCEDURE sp_Insert_Final 
AS
INSERT TS_DATA (doctor, calldate, lastname, firstname, 
	address, city, state, zip, phoneh, phonew, tsr, appt_date, 
	appt_time, status, _rescode, showed, retried, confermer, priority)

SELECT substring(dr, 2, datalength(dr)-1), CONVERT(smalldatetime,calldate),
          ln, fn, addr, city, st, zip,
          phoneh, phonew, tsr, CONVERT(smalldatetime,appt_date), 
          CONVERT(datetime,appt_time), status, 
          CONVERT(smallint, _rescode), CONVERT(char, showed),
	  CONVERT(char,retried), confermer, 
          CONVERT(smallint,substring(priority, 1, datalength(priority)-1))
FROM TEMPDATA
WHERE WhatEverColumnItIs <> ' / / ' OR SomeOtherDateColumn <> ' / / '

You do not need to set a special date format for those in military time.

Tara

TorreyKite
Starting Member

40 Posts

Posted - 2003-08-06 : 17:17:11

ok cool
Thanks alot

TK

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-06 : 17:27:31

BCP isn't too informative when it imports data. It's pretty easy to import an occasional row 1 column off (e.g. I BCP names, and occasionally lastname is 'Jones, Jr.') - That's all it takes is an extra comma.

You might want to run a simple query on TEMPDATE using IsDate before you do the conversion.

SELECT * FROM TEMPDATA WHEN IsDate(calldate) = 0 or IsDate(appt_date) = 0

This'll throw up all the rows with invalid varchar columns that are NOT valid dates, and it'll run before you try to convert the columns.

Sam

TorreyKite
Starting Member

40 Posts

Posted - 2003-08-07 : 09:46:06

Good point sam,
would I have a problem with commas even when my field terminator includes double quotes?
FIELDTERMINATOR =''","''

Also for excluding ' / / '
I wasn't very clear.... I don't want to exclude the entire row. I just want to set the date as blank or NULL. I guess thats why I was thinking of putting an If Then in the select statement.

I think I'll try Select..... ,If IsDate(calldate) = 0 Then ' ' Else calldate , ....

TK

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-07 : 11:56:40

BCP import anomalies are trial and error. I don't know if the comma will give you a problem with your settings or not.

Here's what you want on the SELECT

SELECT ...., case WHEN IsDate(calldate) = 0 THEN '' ELSE calldate END AS calldate

Sam

TorreyKite
Starting Member

40 Posts

Posted - 2003-08-07 : 12:45:24

I got an error:
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.


INSERT TS_DATA (doctor, calldate, lastname, firstname,
        address, city, state, zip, phoneh, phonew, tsr, appt_date, 
	appt_time, status, _rescode, showed, retried, confermer, priority)

SELECT RTRIM(substring(dr, 2, datalength(dr)-1)),  
	  Case WHEN IsDate(calldate) = 0 Then '' Else CONVERT(smalldatetime,RTRIM(calldate)) End AS calldate,
          RTRIM(ln), RTRIM(fn), RTRIM(addr), RTRIM(city), RTRIM(st), RTRIM(substring(zip, 1, 5),
          RTRIM(phoneh), RTRIM(phonew), RTRIM(tsr), 
	  Case WHEN IsDate(appt_date) = 0 Then '' Else CONVERT(smalldatetime,RTRIM(appt_date)) End AS appt_date, 
          Case WHEN IsDate(appt_time) = 0 Then '' Else CONVERT(smalldatetime,RTRIM(appt_time)) End AS appt_time, 
	  RTRIM(status), 
          CONVERT(smallint, RTRIM(_rescode)), CONVERT(char, RTRIM(showed)),
	  CONVERT(char,RTRIM(retried)), RTRIM(confermer), 
          CONVERT(smallint,substring(priority, 1, 1))
FROM TEMPDATA

I also tried this format:


Case IsDate(calldate) WHEN 0 Then '' Else CONVERT(smalldatetime,RTRIM(calldate)) End Case ,

but that said there was a syntax error near case, which one i don't know.
Thanks for the help

TK

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-07 : 16:35:38

You're missing a right parenthesis (in red)..



INSERT TS_DATA (doctor, calldate, lastname, firstname,
        address, city, state, zip, phoneh, phonew, tsr, appt_date, 
	appt_time, status, _rescode, showed, retried, confermer, priority)

SELECT RTRIM(substring(dr, 2, datalength(dr)-1)),  
	  Case WHEN IsDate(calldate) = 0 Then '' Else CONVERT(smalldatetime,RTRIM(calldate)) End AS calldate,
          RTRIM(ln), RTRIM(fn), RTRIM(addr), RTRIM(city), RTRIM(st), RTRIM(substring(zip, 1, 5)),
          RTRIM(phoneh), RTRIM(phonew), RTRIM(tsr), 
	  Case WHEN IsDate(appt_date) = 0 Then '' Else CONVERT(smalldatetime,RTRIM(appt_date)) End AS appt_date, 
          Case WHEN IsDate(appt_time) = 0 Then '' Else CONVERT(smalldatetime,RTRIM(appt_time)) End AS appt_time, 
	  RTRIM(status), 
          CONVERT(smallint, RTRIM(_rescode)), CONVERT(char, RTRIM(showed)),
	  CONVERT(char,RTRIM(retried)), RTRIM(confermer), 
          CONVERT(smallint,substring(priority, 1, 1))
FROM TEMPDATA

At execution time, you *might* get an error if the CASE returns different types in the THEN and ELSE portions. You might want to return NULL instead of '' when an invalid DATE is found.

Why are you using so many TRIM functions?

Sam

TorreyKite
Starting Member

40 Posts

Posted - 2003-08-08 : 09:05:42

wow good catch with the parenthesis...thanks

I was using the trim function to make sure that i did not have any extra spaces.
As you mentioned BCP is not the smoothest of operations. Earlier I got an error while testing saying that one of my fields was not big enough. I could see the data but with a closer look found some extra spaces. so I thought i ought to put trim on everything.

Is this a bad Idea? Will it slow things down alot? (this will be handling millions of records)
Thanks,
TK

TorreyKite
Starting Member

40 Posts

Posted - 2003-08-08 : 11:57:46

grrr... I still get the error with large volumes of data..
I tested the code with 200 records and it works fine.

Then I try it with 100k records and it gives me this:
[code}
Server: Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
{/code}
from what I read in other threads this error means that the data is too large for a particular field. is this the case or does it have anything to do with volume of data (I don't see how I could, but I have to ask).

So does this occur if one record out of 100k does not fit? I guess I thought it would atleast put in the records previous to the invalid record.
*sigh*
well for the time being I'll start searching for records that are too big for the fields.

TK

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-08 : 13:54:52

Clearly you have a bad row or two. Use QA to search for invalid data with:

SELECT * FROM TEMPDATA

WHERE IsDATE(appt_date) = 0

I'd make other suggestions if you post the structure of Table TS_DATA

For example, what type of column is ZIP? Integer or Character? You may be getting an error on an invalid ZIP code?

WHERE IsNumeric(zip) = 0 -- would find invalid Integer zip codes.

Also suggest applying some grooming to TEMPDATA before inserting the data..

UPDATE TEMPDATA
SET columna = LTRIM(RTRIM(columna)) -- and so on

Examine rows with invalid ZIP, date fields, or columns that should be numeric. Look for possible patterns that would identify why the BCP created a bad row from the CSV source file. At the bottom of all this, there's an anomaly in the import that is inserting a few bad rows. If you can find these rows, you might be able to correct the BCP process.

Sam

TorreyKite
Starting Member

40 Posts

Posted - 2003-08-08 : 14:13:55

Just as you posted this I found that my address field was alittle too small. I had allowed 30 characters when apparently West Palm Beach FL has some really long Addresses. so i bumped it up to 50 to be safe.

My zip field is char because Connecticut for example has zips like "06242" which would be come "6242" (I think it would at least).

Here is my TS_DATA table



CREATE TABLE [dbo].[TS_DATA] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[doctor] [nvarchar] (10) NULL ,
	[calldate] [smalldatetime] NULL ,
	[lastname] [nvarchar] (30) NULL ,
	[firstname] [nvarchar] (20) NULL ,
	[address] [nvarchar] (50) NULL ,
	[city] [nvarchar] (25) NULL ,
	[state] [nvarchar] (2) NULL ,
	[zip] [nvarchar] (5) NULL ,
	[phoneh] [nvarchar] (15) NULL ,
	[phonew] [nvarchar] (15) NULL ,
	[tsr] [nvarchar] (10) NULL ,
	[appt_date] [smalldatetime] NULL ,
	[appt_time] [smalldatetime] NULL ,
	[status] [nvarchar] (15) NULL ,
	[_rescode] [smallint] NULL ,
	[showed] [char] (1) NULL ,
	[retried] [char] (1) NULL ,
	[confermer] [nvarchar] (10) NULL ,
	[priority] [smallint] NULL 
) ON [PRIMARY]
GO

thanks for the advice,

TK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-08 : 14:16:25

quote:
Originally posted by TorreyKite

My zip field is char because Connecticut for example has zips like "06242" which would be come "6242" (I think it would at least).

Yes, if you changed it to int, it would become 6242. Will you ever have the extended zip code: 06242-7788? If so, might want to change it to VARCHAR(10).

Why are you using nvarchar instead of varchar?

Tara

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-08 : 14:20:45

In fact, you could drop the city and state entirely and use the zip or zip+4 to infer it. Eliminates spelling errors. The list of all known good zip codes is freely available through usps.gov.

Jonathan
{0}

TorreyKite
Starting Member

40 Posts

Posted - 2003-08-08 : 16:34:50

hmmnn..
nvarchar vs varchar .... well because thats what DTS gave me when I converted the original access db to this.

though almost everything was nvarchar at that time. I've changed alot since.

nvarchar is not in any of the books i have so i figured i'd work on stuff that was not working first and tune it up later.

for now i need to keep the city and state the way they are. This Db is mostly for reports and other analysis of our main CRM DBs. Our CRM system is about 10 years old with a proprietary db system. so i'm limited to mass exports to be able do what i need to do.

In this phase i'm tring to merge several existing information systems to narrow our office down to hhmmnn... only 3 (*sigh* why can't they just upgrade).

ohwell ..

So Yes, Jonathan I actually am using a Zip list like what you mentioned in another aspect of the system but this table needs to reflect what is actually in the old system.
I really appreciate all this help. i'd never be able to accomplish this so quickly with out this forums support. Can't wait till I know some of the answers to other peoples questions.... Only a matter of time.

TK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-08 : 16:40:27

quote:
Originally posted by TorreyKite

hmmnn..
nvarchar vs varchar .... well because thats what DTS gave me when I converted the original access db to this.

If you aren't dealing with Unicode data, then use varchar. nvarchar requires twice the amount of storage space.

Tara

TorreyKite
Starting Member

40 Posts

Posted - 2003-08-08 : 16:45:54

nice,
Will do

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources