Solved

Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Posted on 2008-10-02
10
1,327 Views
Last Modified: 2012-05-05
I was working in a test enviroment on a Windows 2003 Server with SQL Server 2005 and the views I created run fine.  Now I have moved over to our production enviroment and I am receiving an error.
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

There are no bad dates in the table however there is a date of 1/15/1905.  I cannot figure out what is the bad date.  Is there a setting in SQL I can change?  I am running queries against a linked server.
0
Comment
Question by:vzorn
  • 5
  • 4
10 Comments
 

Author Comment

by:vzorn
ID: 22631483
I found the bad date, it is 1/15/1905.  This should be a valid date.  What can I do in SQL to change this?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22636980
Possibly your regional settings are attempting to interpret it as dd/mm/yyyy.

Is it stored in a datetime field or a varchar field?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22637065
>>I am running queries against a linked server.

Is the linked server also a SQL Server 2005?

And is the date field really a DateTime?  It's not a TIMESTAMP, is it?
0
 

Author Comment

by:vzorn
ID: 22637266
The linked server is a Progress database and the field is a varchar.  ;It is a user input field.
The strange thing is it worked in our test enviroment with the same set of data and now this server is erroring.   It is definately stopping at this record with the 1/15/1905.
My query is just a "Select [visit-start-date], [visit-id], [client-id] from LINKEDSRV..PUB.[pt-visit]"
No date parameters.

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22637331
If it's user input data, good luck.

Converting dd/mm/yy from varchar to datetime is:

select convert(datetime, yourfield,103)

mm/dd/yy:
select convert(datetime, yourfield,101)

There's no way to handle this in code reliable.

What if the user enters '02/06/08'?  Is that Feb 6 or June 2?




0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:vzorn
ID: 22639522
The software that is using the Progress database controls the date input as mm/dd/yy.
I will try the convert function.
But why is MS SQL not recognizing 1/15/1905 as a valid date?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22639552
Regional settings perhaps.  Any time you are converting a string to a datetime in SQL server, you are best explicitly setting the format using one of the format codes.  Like I put in http:#22637331.  It will ensure that SQL server is doing it right.  If you data is in mm/dd/yyyy format, instead of just doing:

convert(datetime, yourfield)

do:

convert(datetime,yourfield,101)

If you still get an error, then you have some data that is probably dd/mm/yyyy.
0
 

Author Comment

by:vzorn
ID: 22645546
It is almost working.  The query below works fine, but I need to pass a parameter.  I get the following error when I pass a parameter.

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

SELECT     pt_visit.[client-id], CAST(pt_visit.[discipline-code] AS nvarchar(50)) AS DisciplineCode, CONVERT(DATETIME,pt_visit.VisitStartDateChar) AS VisitStartDate, 

                      ts_entry.[sub-type-id], ts_entry.[visit-id]

FROM         dbo.view_pt_visit AS pt_visit INNER JOIN

                      dbo.view_ts_entry AS ts_entry ON pt_visit.[visit-id] = ts_entry.[visit-id]

WHERE     (ts_entry.[type-id] = 3) AND (isDate(pt_visit.VisitStartDateChar) = 1) AND (CAST(pt_visit.VisitStartDateChar AS DATETIME) > '1/1/1900'))
 
 

BUT THIS GIVES ME THE ERROR:

SELECT * FROM (

SELECT     pt_visit.[client-id], CAST(pt_visit.[discipline-code] AS nvarchar(50)) AS DisciplineCode, CONVERT(DATETIME,pt_visit.VisitStartDateChar) AS VisitStartDate, 

                      ts_entry.[sub-type-id], ts_entry.[visit-id]

FROM         dbo.view_pt_visit AS pt_visit INNER JOIN

                      dbo.view_ts_entry AS ts_entry ON pt_visit.[visit-id] = ts_entry.[visit-id]

WHERE     (ts_entry.[type-id] = 3) AND (isDate(pt_visit.VisitStartDateChar) = 1) AND (CAST(pt_visit.VisitStartDateChar AS DATETIME) > '1/1/1900'))

AS C WHERE CAST(C.VisitStartDate as DateTime) >='1/1/2008'
 

I HAVE TRIED:
 

WHERE CAST(C.VisitStartDate as DateTime) >='1/1/2008'

WHERE CAST(C.VisitStartDate as DateTime) >='2008-01-01'

WHERE CONVERT(DATETIME,C.VisitStartDate,103) >='1/1/2008'

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22647110
You would be best served to do a cast of your date.  Like I mentioned earlier, your regional settings are probably doing it.

Wrap your date in this.

select convert(datetime, '1/1/2008', 101) -- mm/dd/yyyy
or
select convert(datetime, '1/1/2008', 103) -- dd/mm/yyyy

0
 

Author Comment

by:vzorn
ID: 22704774
Turns out if I put an isDate() in the WHERE clause before the CONVERT(DATETIME,value) it works.

Thanks for your help.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now