Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Unable to Convert varchar (Date) to datetime

Posted on 2004-04-21
14
Medium Priority
?
1,035 Views
Last Modified: 2012-06-27
Hi,

I tried to manually change a few fields in my table that originally had datatypes of "varchar" to "datetime".  I am hoping that by converting to datetime, then I can display specific records in a range according to dates. However, when I went directly to my table and changed the value type of the field, authorization was denied. I got the following error messages:

1) Data may be lost converting
2) Then when I click okay, I get the following:

3) 'BBDirectory_BPD (dbname)' table
- Unable to modify table.  
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

  How can I successfully convert the datatype from varchar to datetime?


Thanks in advance.
0
Comment
Question by:synergeticsoul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10880986
It looks like your varchar-dates are not compatible with your database date format.
Try to issue UPDATE command on your table converting strings to appropriate format. I can't help you further because I don't know what format use your current column and what is used for datetime in DB...
0
 

Author Comment

by:synergeticsoul
ID: 10881309
Although varchar was being used, dates were entered as follows: mm/dd/yy for all date-related fields.  

how would I correctly use the update command?  

Thanks.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 10881401
Hmm, that conversion should work OK IF all dates are correct, so they must not be :-).  Try this to see if you have any bad columns:


SELECT *
FROM tableName
WHERE ISDATE(columnName) <> 1
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 10881405
Once you correct (or delete) the invalid columns, then you can use ALTER to change the datatype of the column.
0
 
LVL 5

Assisted Solution

by:chaniewskim
chaniewskim earned 1000 total points
ID: 10881506
If all dates are OK, then try it in QueryAnalyzer:

SET DATEFORMAT mdy
GO
ALTER TABLE YourTable ALTER COLUMN YourColumn datetime
GO
0
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10881520
I meant:

If all dates are OK, and you still cannot convert that column, then...
0
 

Author Comment

by:synergeticsoul
ID: 10881542
ScottPletcher,

I think that is the problem, I can not alter the datatype of the column.

For example, one of the 4 columns I need to convert is STARTDATE.  The datatype is currently varchar, 100.  I want to convert the datatype to datetime so that I can display the dates in a range through my query.  

How can I change the datatype without producing errors?  There are currently over 800 records and my head is on a plate.

Thanks.
0
 

Author Comment

by:synergeticsoul
ID: 10881570
Additionally, through my programming (coding in CFML), I have dates inserted into the fields as: mm/dd/yy.
0
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10881575
As Scott has written,

SELECT *
FROM tableName
WHERE ISDATE(columnName) <> 1

will return all rows which make problems with conversion. Then you probably have to correct them manually...
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10881594
it means that a) your server may be expecting a character dates to be in the format dd/mm/yy or b) one of your dates made in it wrong

If its a - then there are a few ways around. To find out easily, there are many ways but this quick query will do it

select convert(datetime, '12/31/99')

if it produces an error, then your server is expecting dd/mm/yy and you can parse out your input to change it.

If you get no errors from above, then you have 1 (or more) value(s) in your table that is wrong.
Finding it can be a pain. One way I've done this in the past (there are other ways) is
select identityfield, originalfield, convert(datetime,originalfield)
from table
order by identityfield

It will run, and produce results until it finds one record that is wrong. And you will know the last valid identityfield.
Then you can
select top 1 identityfield, originalfield
from table
where identityfield > [lastvalididentity]
order by identityfield

Then determine what the correct date should be, update the record and repeat the process.

This works if you have an identity field if you dont, then order by and select by the date field.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 10881627
Yes, chaniewskim is right, most likely you will need to correct them "manually", which might include an UPDATE statement(s) if certain conditions occurred often, that is, the same mistake was made multiple times.  

For example, let's say that several times the '/'s are omitted, so some data looks like this:

011304   (mmddyy = 01/13/04)
022704


Then you could do this:

UPDATE yourTable
SET dateColumn = LEFT(dateColumn, 2) + '/' + SUBSTRING(dateColumn, 3, 2) + '/' + SUBSTRING(dateColumn, 5, 2)
WHERE CHARINDEX('/', dateColumn) = 0
--AND ...any other needed qualifier(s)...


Note that of course the qualifiers are very important -- otherwise you'll damage existing dates that are already correct.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 10881642
Chris:

ISDATE(), unlike say ISNUMERIC(), is pretty reliable (at least I've never had a problem with it).  It can be used to find all invalid dates at one time.
0
 

Author Comment

by:synergeticsoul
ID: 10887488
Thank you all.  I am going to try your suggestions.
0
 

Author Comment

by:synergeticsoul
ID: 10901032
I was able to successfully pull the records using

SELECT *
FROM tableName
WHERE ISDATE(columnName) <> 1

There were only a few that had the incorrect formatting.  Thank You!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question