June 28, 2006 at 2:42 am
How do I move a varchar string like '2004-05-13 00:00:00.000' into a datetime column when moving these strings from one table to another?
June 28, 2006 at 2:48 am
If necessary, use
INSERT DestinationTable (DestField)
SELECT CONVERT(DATETIME, MyField, 121) FROM SourceTable
121 is not needed. It is just there for enforcing yyyy-mm-dd hh:mm:ss.mmm format. SQL Server tries to do that automatically when not present.
N 56°04'39.16"
E 12°55'05.25"
June 28, 2006 at 2:58 am
When I try that I get the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string. (SQL-22007)(DBD: Execute immediate failed err=-1)
This error is in the following context:
CREATE TABLE Slimmed_down_admissions (
PrimDiag varchar(6) NOT NULL,
Spec int NOT NULL,
Age int NOT NULL,
Sex int NOT NULL,
OutputArea varchar(12) NOT NULL,
DischargeDate datetime NOT NULL,
EpiStartDate datetime NOT NULL,
EpiEndDate datetime NOT NULL,
PatClas int NOT NULL,
GP varchar(8) NOT NULL,
Practice varchar(8) NOT NULL
)
INSERT INTO Slimmed_down_admissions (PrimDiag, Spec, Age, Sex, OutputArea, DischargeDate, EpiStartDate, EpiEndDate, PatClas, GP, Practice)
SELECT PrimDiag, CONVERT(INT, Spec), CONVERT(INT, Age), CONVERT(INT, Sex), OutputArea, CONVERT(DATETIME, DischargeDate), CONVERT(DATETIME, EpiStartDate), CONVERT(DATETIME, EpiEndDate), CONVERT(INT, PatClas), GP, Practice
FROM Admissions_data A
WHERE A.DoncResident = 'Y'
AND A.DominantEpisode = 'Y'
AND PrimDiag IS NOT NULL
AND Spec IS NOT NULL
AND Age IS NOT NULL
AND Sex IS NOT NULL
AND OutputArea IS NOT NULL
AND DischargeDate IS NOT NULL
AND EpiStartDate IS NOT NULL
AND EpiEndDate IS NOT NULL
AND PatClas IS NOT NULL
AND GP IS NOT NULL
AND Practice IS NOT NULL
AND OutputArea LIKE '__________'
June 28, 2006 at 3:01 am
I think we are missing some information here. It seems that the ODBC driver does not recognize the datetime format.
What are you trying to accomplish? Inserting rows from an outside system, into SQL Server? If so, trim the .mmm part first. If not using time either, trim that too.
Help us helping you by giving us all relevant information.
N 56°04'39.16"
E 12°55'05.25"
June 28, 2006 at 3:05 am
Sorry, just updated my last post as you posted your new post. I'm not sure whether I have detailed enough in the update.
June 28, 2006 at 3:35 am
You are using ODBC to copy from one table in SQL to another table in SQL?
Create a stored procedure to do that for you, and call the SP from your client application. This way, there will not be any conversion errors.
N 56°04'39.16"
E 12°55'05.25"
June 28, 2006 at 4:39 am
I am using stored procedures that are activated via a ODBC connection. The error message is what comes through to the DOS screen when I run my Perl programs. I should have taken out that ODBC stuff. No added confusion intended.
Anyway, back to the problem, I have tried to change the data types in the source table, using enterprise manage (not T-SQL). After much playing around I have changed three of the varchar columns to datetime. I'm not really sure how I managed to do it and consequently I am not able to change the forth column that needs changing. So in summary, the the varchar strings in question must be recognisable as a date. Just how I get it to recognise this I am not sure. Maybe I need to convert it to an intermediate datatype before it works. Can anyone offer suggestions? The annoying thing is that I have already converted three of the columns. I just don't know how I did it. Maybe it is MS SQL Server 2000 issue.
June 28, 2006 at 5:36 am
I think one row or more in the Admissions_data table contains an invalid date. This is the most likely scenario.
Which is the fourth column of date information? Is this column allowed to keep NULL? And the destination table is not?
Run this code against your table!
SELECT DischargeDate,
ISDATE(DischargeDate),
EpiStartDate,
ISDATE(EpiStartDate),
EpiEndDate,
ISDATE(EpiEndDate),
<fourth column>,
ISDATE(<fourth column> )
FROM Admissions_data
WHERE ISDATE(DischargeDate) = 0
OR ISDATE(EpiStartDate) = 0
OR ISDATE(EpiEndDate) = 0
OR ISDATE(<fourth column> ) = 0
N 56°04'39.16"
E 12°55'05.25"
June 29, 2006 at 2:38 am
There are some rows that return 0s in the Expr1, Expr2, Expr3 and Expr4 columns. Does that indicate a faulty form with some of the entires?
June 29, 2006 at 4:17 am
Yes, the SQL Server can't interpret those days show, as valid dates for various reasons.
One reason can be that the date format has changed, for example from d-m-y to m-d-y or any other format. Maybe there is a date 2007-02-29 which is not valid. Or dates missing at all or dates with zero as date, or month. Or maybe not an date at all. Can you post the list of invalid dates here?
N 56°04'39.16"
E 12°55'05.25"
June 29, 2006 at 4:38 am
Sorry, I didn't look closely enough at the output. All the '0's in the output are due to NULL values. But the string value 'NULL' and not the <NULL>. I really need a SQL statement that will convert 'NULL' to <NULL> in every column, in every row.
June 29, 2006 at 4:39 am
Sorry, I didn't look closely enough at the output. All the '0's in the output are due to NULL values. But the string value 'NULL' and not the <NULL>. I really need a SQL statement that will convert 'NULL' to <NULL> in every column, in every row.
June 29, 2006 at 4:46 am
Let us continue to use the ISDATE function as follows for generic solution
INSERT Slimmed_down_admissions
(
PrimDiag,
Spec,
Age,
Sex,
OutputArea,
DischargeDate,
EpiStartDate,
EpiEndDate,
PatClas,
GP,
Practice
)
SELECT PrimDiag,
CONVERT(INT, Spec),
CONVERT(INT, Age),
CONVERT(INT, Sex),
OutputArea,
CASE WHEN ISDATE(DischargeDate) = 1 THEN DischargeDate END,
CASE WHEN ISDATE(EpiStartDate) = 1 THEN EpiStartDate END,
CASE WHEN ISDATE(EpiEndDate) = 1 THEN EpiEndDate END,
CONVERT(INT, PatClas),
GP,
Practice
FROM Admissions_data A
WHERE A.DoncResident = 'Y'
AND A.DominantEpisode = 'Y'
AND PrimDiag IS NOT NULL
AND Spec IS NOT NULL
AND Age IS NOT NULL
AND Sex IS NOT NULL
AND OutputArea IS NOT NULL
AND DischargeDate IS NOT NULL
AND EpiStartDate IS NOT NULL
AND EpiEndDate IS NOT NULL
AND PatClas IS NOT NULL
AND GP IS NOT NULL
AND Practice IS NOT NULL
AND OutputArea LIKE '__________'
and you should be ok. Good luck!
Or, if you prefer a 'NULL' solution only, use following
INSERT Slimmed_down_admissions
(
PrimDiag,
Spec,
Age,
Sex,
OutputArea,
DischargeDate,
EpiStartDate,
EpiEndDate,
PatClas,
GP,
Practice
)
SELECT PrimDiag,
CONVERT(INT, Spec),
CONVERT(INT, Age),
CONVERT(INT, Sex),
OutputArea,
CASE WHEN ISNULL(DischargeDate, 'NULL') <> 'NULL' THEN CONVERT(DATETIME, DischargeDate) END,
CASE WHEN ISNULL(EpiStartDate, 'NULL') <> 'NULL' THEN CONVERT(DATETIME, EpiStartDate) END,
CASE WHEN ISNULL(EpiEndDate, 'NULL') <> 'NULL' THEN CONVERT(DATETIME, EpiEndDate) END,
CONVERT(INT, PatClas),
GP,
Practice
FROM Admissions_data A
WHERE A.DoncResident = 'Y'
AND A.DominantEpisode = 'Y'
AND PrimDiag IS NOT NULL
AND Spec IS NOT NULL
AND Age IS NOT NULL
AND Sex IS NOT NULL
AND OutputArea IS NOT NULL
AND DischargeDate IS NOT NULL
AND EpiStartDate IS NOT NULL
AND EpiEndDate IS NOT NULL
AND PatClas IS NOT NULL
AND GP IS NOT NULL
AND Practice IS NOT NULL
AND OutputArea LIKE '__________'
N 56°04'39.16"
E 12°55'05.25"
June 29, 2006 at 4:59 am
Just to note that you can use the 'NULLIF' function too (example below). But the ISDATE approach is probably safer and better.
--data
declare @t table (d1 varchar(30), d2 varchar(30))
insert @t
select '20060629', 'NULL'
union all select 'NULL', NULL
--calculation
select cast(nullif(d1, 'NULL') as datetime), cast(nullif(d2, 'NULL') as datetime) from @t
/*results
-------------------------- ----------------------
2006-06-29 00:00:00.000 NULL
NULL NULL
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
June 29, 2006 at 5:02 am
Faster too, I presume.
Since the function ISDATE already have checked that the field is a valid date, there is no need to cast the field one more time before inserting.
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply