July 1, 2008 at 9:03 am
Hi
INSERT INTO valid_clinical ([Inc Date])
SELECT convert(datetime, [Incident date], 101)
FROM dbo.base_clinical
I'm trying to isert some data from one table to another. However a data field [Incident date] in base_clinical table is an nvarchar(12) and a datetime in valid clinical. I'm getting a conversion error:
"Syntax error converting datetime from character string"
How can I insert the data as a date?
Kind Regards Bill Humphrey
July 1, 2008 at 9:11 am
can you post and example of [Incident date] ?
July 1, 2008 at 9:12 am
Try this:
SELECT [Incident date]
FROM dbo.base_clinical
where isdate([Incident date]) = 0
That will give you the rows where it can't convert that column to a date. From there, you can probably figure out how to clean up the data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 2:04 am
Here are some samples:
12/31/2006
1/1/2007
12/27/2006
12/3/2007
The code above pulls back all the records because the data is currently nvarchar(12) and not datime
July 2, 2008 at 7:17 am
Did you actually try the isdate() function, as per the code I wrote? What that does is return a 0 for strings that can't be converted directly to dates, and a 1 for strings that can be converted directly to dates.
For example:
select isdate(N'1/1/2000'), isdate('GSquared'), isdate(N'100/100/2000')
returns:
1 0 0
because '1/1/2000' as a string (varchar/nvarchar/char/nchar) that can be converted/cast directly to datetime, but 'GSquared' and '100/100/2000' can't. (One's a non-date string, the other is out-of-range.)
If you ran that query and it returned every row in your table, then none of the data is actually formatted in such a way that it can be converted to dates. That means out-of-range, or has non-date text in it, or whatever.
If your sample data actually matches what you posted, it won't show up in that query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply