Error converting data type DBTYPE_DBTIMESTAMP to datetime.

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.
vzornAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
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
 
vzornAuthor Commented:
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
 
BrandonGalderisiCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Daniel WilsonCommented:
>>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
 
vzornAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
vzornAuthor Commented:
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
 
vzornAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
vzornAuthor Commented:
Turns out if I put an isDate() in the WHERE clause before the CONVERT(DATETIME,value) it works.

Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.