data type conversion issue
-
Hi guys
I exported some data from a text file to sql server. Here is the sample data..
This table has about 2 million rows.There is a date field in the table which comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i get an error as operation timed out..
Conversion to 'datetime' is really crucial .
How do i do this??
-
There is no any test data we can see.
But anyway you must read "CAST and CONVERT" topic in BOL at least 10 times before you start to do any conversions from text to datetime.
_____________
Code for TallyGenerator -
Here is the data from the text file...
Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
I have tried the bulk insert as well.
Here is the script for the create table ..
USE
[Library]
GO
/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TABLE [dbo].[NormalOutlier1](
[Datedispensed] [nvarchar]
(max) COLLATE Latin1_General_CI_AS NULL,
[Outliers] [nvarchar]
(max) COLLATE Latin1_General_CI_AS NULL,
[Formulation ID] [float]
NULL,
[Provider Number (dispensing)] [nvarchar]
(max) COLLATE Latin1_General_CI_AS NULL,
[NSS flag] [nvarchar]
(max) COLLATE Latin1_General_CI_AS NULL,
[Patient category] [nvarchar]
(max) COLLATE Latin1_General_CI_AS NULL,
[Units dispensed] [float]
NULL,
[Total days supply] [float]
NULL
)
ON [PRIMARY]
Hope this helps
-
I'm a bit confused... are you trying to import the file (your comment about Bulk Insert) or are you trying to export to a text file and have the date go without the time? If you are trying to export, are you using BCP or OSQL? If you are trying to import, does the text file look exactly like the one you posted?
Also, if you are trying to import the data, what format is the date in? MM/DD/YYYY or DD/MM/YYYY? In either case, why isn't the two digit rule (ie.01/01/2006 instead of 1/01/1006) being used? If you are trying to export the data, what format DO YOU want the date in?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If [Datedispensed] is a date why datatype is [nvarchar](max) ?
You know, mita, conversion of 2 million values takes some time. And remember, you need to pump it to EM, display on screen...
And datatype [nvarchar](max) lets you think less but makes your solutions worse.
Don't let this troyan horse inside of your place.
_____________
Code for TallyGenerator -
Hi Sergiy
This is the format which sql recognises.. Its not any kind of virus.. its the data which we recieve from our clients on weekly basis..
And what do you mean when you say 'EM'??
Sorry for my lack of knowledge
Thanks
-
What do you mean when you say it "timed out"? What app are you in?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
sql server 2005
-
Then I think you need to take Serqiy's advice... he wasn't talking "Trojan Horse" as in "Virus"... he was talking about taking the easy way out with NVARCHAR(MAX) which is actually worse than declaring everything as a Variant so far as performance goes... I share his concern in trying to understand why anyone would store a date in an NVARCHAR column or a small name in a datatype capable of holding 2 billion characters or why you need to store something that'll never have more than 2 decimal places as a FLOAT. That's "one" problem...
I've not had the pleasure nor the pain of using SQL Server 2005 so I'll have to equate the timeout in terms I know...
There are two main applications in SQL Server 2000... Enterprise Manager (usually referred to as just "EM") and Query Analyzer (usually referred to as just "QA). If you run a seemingly simple query in EM, you will frequently get a timeout. Why? Dunno and almost don't care... but it happens with more regularity than I care for. If you run the exact same query in QA, you won't get the timeout. It's built for a different purpose than EM was...
I suspect the same thing is happening to you... you're just running it from the wrong "window" in SQL Server 2005. (Not to mention that your table is "bloated" with way out of scope datatypes).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply