I have a varchar field containing dates that I am trying to convert to a
smalldatetime but I keep getting the following error message:
Syntax error converting datetime from character string.
Here is the line of code I am using:
select convert(smalldatetime, DateReported, 101)
from tblName
I checked to make sure there were only valid dates in the field using IsDate
so I am not sure why I can't convert the data to a smalldatetime datatype.
Thanks!any sample data? can you locate the record that causes problem? or all of
them are problematic? maybe the 101 format is wrong?
peter|||It would help if you post a sample of the data, but it appears there is at
least one row that has some extra special character which is the one that
causing the error at the time of the conversion.
You can try executing
select top 1 convert(smalldatetime, DateReported, 101)
from tblName
to verify that not all the values have an issue.
Let me know
"Anonymous" wrote:
> I have a varchar field containing dates that I am trying to convert to a
> smalldatetime but I keep getting the following error message:
> Syntax error converting datetime from character string.
> Here is the line of code I am using:
> select convert(smalldatetime, DateReported, 101)
> from tblName
> I checked to make sure there were only valid dates in the field using IsDa
te
> so I am not sure why I can't convert the data to a smalldatetime datatype.
> Thanks!
>
>|||You guys are right. The data imported with an extra character.
I changed my insert statement to:
Insert tblNew
Select LTrim(RTrim([DateReported]))
From tblOld
However, the extra space is still being inserted into the field. How do I
insert the data with no trailing spaces?
"Edgardo Valdez, MCSD, MCDBA" wrote:
> It would help if you post a sample of the data, but it appears there is at
> least one row that has some extra special character which is the one that
> causing the error at the time of the conversion.
> You can try executing
> select top 1 convert(smalldatetime, DateReported, 101)
> from tblName
> to verify that not all the values have an issue.
> Let me know
> "Anonymous" wrote:
>|||You can use the T-SQL function RTRIM and or LTRIM
Let me know if that helps
"Anonymous" wrote:
> You guys are right. The data imported with an extra character.
> I changed my insert statement to:
> Insert tblNew
> Select LTrim(RTrim([DateReported]))
> From tblOld
> However, the extra space is still being inserted into the field. How do I
> insert the data with no trailing spaces?
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||I tried that but it is still inserting the extra space. This is the code I
used to insert the data into the new table:
Insert tblNew
Select LTrim(RTrim([DateReported]))
From tblOld
"Edgardo Valdez, MCSD, MCDBA" wrote:
> You can use the T-SQL function RTRIM and or LTRIM
> Let me know if that helps
> "Anonymous" wrote:
>|||You can try the REPLACE function
Insert tblNew
select replace(DateReported, ' ', '')
From tblOld
"Anonymous" wrote:
> I tried that but it is still inserting the extra space. This is the code
I
> used to insert the data into the new table:
> Insert tblNew
> Select LTrim(RTrim([DateReported]))
> From tblOld
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Nope, that doesn't work either. Even tried a LTrim RTrim and replace update
on the data once it was in the table.
Is there a line of code to see what the ascii character is that is being
used. Maybe it isn't a space.
"Edgardo Valdez, MCSD, MCDBA" wrote:
> You can try the REPLACE function
> Insert tblNew
> select replace(DateReported, ' ', '')
> From tblOld
> "Anonymous" wrote:
>|||Well you can use ASCII to inspect a character at a time:
eg
SELECT ASCII('a') -- Returns 97
Write a loop to look at your string. I myself had big problems when
importing some Null Characters ( ascii code 0 )
SELECT ASCII(CHAR(0))
You can't use REPLACE on these and I had to use STUFF! Whoever uses STUFF
Kev!? You could use a low-level text editor to look at these. But why worr
y
about that? Just use SUBSTRING to extract the bit of string you want. ie i
f
you know your funny character is at the start of your string use SUBSTRING (
your_date, 2, 10 ) for example
Let me know how you get on.
Damien
"Anonymous" wrote:
> Nope, that doesn't work either. Even tried a LTrim RTrim and replace upda
te
> on the data once it was in the table.
> Is there a line of code to see what the ascii character is that is being
> used. Maybe it isn't a space.
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Can you show use the definition of tblNew as well as tblOld?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:2157F90B-2B55-4AB6-AD14-0F64D506F792@.microsoft.com...
>I tried that but it is still inserting the extra space. This is the code I
> used to insert the data into the new table:
> Insert tblNew
> Select LTrim(RTrim([DateReported]))
> From tblOld
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>
