Code that works in SQL Server 2005, but not in SQL Server 2008R2

Question

Post reply

Code that works in SQL Server 2005, but not in SQL Server 2008R2

PFlorenzano-641896

SSCommitted

Points: 1643
More actions
November 4, 2010 at 12:00 pm

#226043

Hello,
I have a piece of code that works great in SQL Server 2005, but not in SQL Server 2008R2. Below is the code:
declare @thisdate datetime;
set @thisdate = getdate();
SELECT
cv.currentlocation,
cv.ClientDisplayName,
cv.IDCode as MRN,
cv.VisitIDCode as AccountNum,
cv.visitstatus,
convert(varchar,cv.admitdtm,101)admitdt,
cv.providerdisplayname,
o.name,
o.summaryline,
oto.TaskStatusCode,
dateadd (dd,datediff(dd,0,oto.SignificantDtm),0)as DateAdministered,
oto.SignificantTime as TimeAdministered,
oto.SummaryLine,
oto.TaskComment,
tud.value AS PMFDATE,
DATEDIFF(minute, oto.performedfromdtm, tud.value) AS Duration
FROM CV3Order o (nolock)
INNER JOIN CV3ClientVisit cv (nolock) ON o.ClientGUID = cv.ClientGUID
AND o.chartGUID = cv.chartGUID
INNER JOIN CV3OrderTaskOccurrence oto (nolock) ON o.GUID = oto.OrderGUID
inner join cv3taskuserdata tud (Nolock)on tud.taskoccurrenceguid = oto.guid
--WHERE dateadd (dd,datediff(dd,0,oto.SignificantDtm),0)= dateadd(dd,datediff(dd,0,@thisdate)-1,0) -- -1 used to capture data from previous date
WHERE tud.userdatacode = 'PRX IVPB EndDTM'
and oto.taskstatuscode = 'Performed'
and (cv.visitstatus = 'adm' or cv.visitstatus = 'dsc')
order by cv.currentlocation asc,oto.significantdtm asc
The code in boldface fails in 2008 with Conversion failed when converting date and/or time from character string., but works fine in SQL Server 2005. The database is identical to each environment, with the excepetion of the version.
Should I be using different commands?
Thank you for your answers and suggestions.
PMF

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Michael Lee-169622 SSCommitted Points: 1721 More actions · Answer 1

Possibly DATEDIFF(minute, CAST(oto.performedfromdtm AS smalldatetime), CAST(tud.value AS smalldatetime))?

Shalom!,

Michael Lee

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 2

Hello Mike,

Thank you for the reply. The code still fails with the same error.

PMF

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 3

What happens if you use "mi" or "n" instead of "minute" for the interval argument?

The Redneck DBA

Michael Lee-169622 SSCommitted Points: 1721 More actions · Answer 4

Dumb question, but you have checked to make sure the oto.performedfromdtm and tud.value are dates?

Shalom!,

Michael Lee

Lowell SSC Guru Points: 323518 More actions · Answer 5

i'm guessing that tud.value is stored as a varchar instead of an actual datetime field?

could it be one or more values are in that column , say, english date format(DMY instead of american date format(MDY)?

31-11-2010 would throw that error if it existed in the data.

try adding SET DATEFORMAT DMY or SET DATEFORMAT MDY

and rerun the query...does it make a difference?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 6

Thanks guys.

Yes the tud.value column is a varchar column, but further down in the query within the WHERE clause, the tud.userdatacode = 'PRX IVPB EndDTM' criteria is limiting only date like fields.

I tried changing the word MINUTE to either MM or N with no such luck, as well as the SET DATEFORMAT YMD; field.

PMF

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

You must also still have the database compatibility level set to 2005, because this line of code would give an error in 2008:

FROM CV3Order o (nolock)

The correct syntax in 2008 would be:

FROM CV3Order o with (nolock)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

PFlorenzano-641896 (11/4/2010)
Thanks guys.
Yes the tud.value column is a varchar column, but further down in the query within the WHERE clause, the tud.userdatacode = 'PRX IVPB EndDTM' criteria is limiting only date like fields.
I tried changing the word MINUTE to either MM or N with no such luck, as well as the SET DATEFORMAT YMD; field.
PMF

That doesn't limit tud.value to values that can be converted to dates. It might limit it to values that are supposed to be dates.

You should use the ISDATE function to find the values in tud.value that are bad.

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 9

The compatibility mode is set to 100 on the SQL Server 2008 R2 server, and 90 on the SQL Server 2005 server.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 10

PFlorenzano-641896 (11/4/2010)
The compatibility mode is set to 100 on the SQL Server 2008 R2 server, and 90 on the SQL Server 2005 server.

I ran into different compatibility modes giving me fits quite a bit when our office started moving from 2000 to 2005...and it would often feed me error messages that had nothing to do with what the problem was. Might be worth trying to switch the compatibility mode on the 2008 box to 90 and see if the query magically runs. (Probably best to put a copy of that DB on a test box instead of changing the compatibility mode on a production server...)

The Redneck DBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 11

Can you show us the output from these queries, please? It'll give us an idea of what the data looks like in the column.

SELECT top 20

oto.performedfromdtm

FROM

CV3OrderTaskOccurrence oto (nolock)

WHERE

ISDATE( oto.performedFromDTM) = 0

SELECT top 20

oto.performedfromdtm

FROM

CV3OrderTaskOccurrence oto (nolock)

WHERE

ISDATE( oto.performedFromDTM) = 1

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 12

Thanks Craig,

Below is the result set from the queries that you sent me:

SELECT top 20

oto.performedfromdtm

FROM

CV3OrderTaskOccurrence oto (nolock)

WHERE

ISDATE( oto.performedFromDTM) = 0

NULL

SELECT top 20

oto.performedfromdtm

FROM

CV3OrderTaskOccurrence oto (nolock)

WHERE

ISDATE( oto.performedFromDTM) = 1

2008-05-24 05:39:00.000

2009-12-14 13:48:00.000

2010-04-23 13:49:00.000

2010-04-24 08:30:00.000

2010-04-25 08:32:00.000

2010-04-26 08:14:00.000

2010-04-27 08:35:00.000

2010-04-22 18:10:00.000

2010-04-24 08:01:00.000

2010-04-22 18:07:00.000

2010-04-22 18:52:00.000

2010-04-23 00:32:00.000

2010-04-23 01:55:00.000

2010-04-23 10:04:00.000

2010-04-23 15:51:00.000

2010-04-24 00:43:00.000

2010-04-24 07:58:00.000

2010-04-24 10:02:00.000

2010-04-24 16:16:00.000

2010-04-25 06:07:00.000

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 13

Whoops, well, answers one question.

Another try for the first query, please?

SELECT top 20

oto.performedfromdtm

FROM

CV3OrderTaskOccurrence oto (nolock)

WHERE

ISDATE( oto.performedFromDTM) = 0

AND oto.performedFromDTM IS NOT NULL

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

PFlorenzano-641896 SSCommitted Points: 1643 More actions · Answer 14

No result set came back from the second query.

Thank you 🙂