November 4, 2010 at 12:00 pm
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
November 4, 2010 at 12:10 pm
Possibly DATEDIFF(minute, CAST(oto.performedfromdtm AS smalldatetime), CAST(tud.value AS smalldatetime))?
Michael Lee
November 4, 2010 at 12:12 pm
Hello Mike,
Thank you for the reply. The code still fails with the same error.
PMF
November 4, 2010 at 12:19 pm
What happens if you use "mi" or "n" instead of "minute" for the interval argument?
The Redneck DBA
November 4, 2010 at 12:21 pm
Dumb question, but you have checked to make sure the oto.performedfromdtm and tud.value are dates?
Michael Lee
November 4, 2010 at 12:23 pm
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
November 4, 2010 at 12:37 pm
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
November 4, 2010 at 12:38 pm
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)
November 4, 2010 at 12:43 pm
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.
November 4, 2010 at 12:54 pm
The compatibility mode is set to 100 on the SQL Server 2008 R2 server, and 90 on the SQL Server 2005 server.
November 4, 2010 at 12:59 pm
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
November 4, 2010 at 1:13 pm
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
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
November 4, 2010 at 1:19 pm
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
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
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
November 4, 2010 at 2:08 pm
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
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
November 4, 2010 at 2:23 pm
No result set came back from the second query.
Thank you 🙂
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply