Out-Of-Range Value error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Out-Of-Range Value error

Author

Topic

mpayne2010
Starting Member

14 Posts

Posted - 2013-07-11 : 15:48:31

When I run this query, I am getting the converesion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

When I run this against my test database - it runs perfectly; however when I put it into the production database, I get the above error. Any help GREATLY appreciated. The error is related to the Insertion_on_Board field.

DECLARE @ClientVisitLocation Table (
ClientDisplayName Varchar (100),
VisitIDCode varchar (30),
IDCode Numeric (15,0),
Age Numeric (15,0),
Insertion_On_Board DateTime)

Insert Into @ClientVisitLocation

Select Distinct cv.clientdisplayname
,cv.VisitIDCode
,cv.idcode MRN
,DATEDIFF(yy, convert(datetime, cast(cl.BirthMonthNum as nvarchar) + '/' + cast(cl.BirthDayNum as nvarchar) + '/' + cast(cl.BirthYearNum as nvarchar)), cv.AdmitDtm)
,Convert(Varchar(20),ins.createdwhen,120)

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-11 : 15:51:48

the query you posted is truncated can you post the complete query

mpayne2010
Starting Member

14 Posts

Posted - 2013-07-11 : 15:57:19

Sure :)

DECLARE @ClientVisitLocation Table (
ClientDisplayName Varchar (100),
VisitIDCode varchar (30),
IDCode Numeric (15,0),
Age Numeric (15,0),
Insertion_On_Board DateTime)

Insert Into @ClientVisitLocation

Select Distinct cv.clientdisplayname
,cv.VisitIDCode
,cv.idcode MRN
,DATEDIFF(yy, convert(datetime, cast(cl.BirthMonthNum as nvarchar) + '/' + cast(cl.BirthDayNum as nvarchar) + '/' + cast(cl.BirthYearNum as nvarchar)), cv.AdmitDtm)
,Convert(Varchar(20),ins.createdwhen,120)

from sxaedlocationaudit ela

inner join cv3clientvisit cv
on cv.guid = ela.clientvisitguid

inner join cv3client cl
on cl.guid = cv.ClientGUID

inner join CV3clientvisitlocation cvl
on cvl.clientguid = cv.ClientGUID
and cvl.clientvisitguid = cv.guid

inner join cv3location l
on l.guid = cvl.locationguid

-- INSERT - When Patient was placed on the tracking board via registration
Left Outer Join sxaedlocationaudit ins
on ins.ClientVisitGUID = ela.ClientVisitGUID
and ins.columnName = 'INSERT'
and ins.CreatedWhen = (SELECT min(CreatedWhen)
FROM SXAEDLocationAudit
WHERE ClientVisitGUID = ins.ClientVisitGUID
and ColumnName = 'INSERT')

-- TIP - when seen by physician --

Left Outer Join sxaedlocationaudit tip
on tip.clientvisitguid = ela.clientvisitguid
and tip.columnName = 'STS'
and tip.ColumnNewValue = 'TIP'
and tip.CreatedWhen =(Select Max(createdwhen)
FROM SXAEDLocationAudit
WHERE ClientVisitGUID = TIP.ClientVisitGUID
and ColumnName = tip.ColumnName
and ColumnNewValue = ('TIP'))

-- First TBADM is first decision to Admit

Left Outer Join sxaedlocationaudit tbadm
on tbadm.clientvisitguid = ela.clientvisitguid
and tbadm.columnName = 'STS'
and tbadm.ColumnNewValue in ('TBADM', 'Pre-Adm')
and tbadm.CreatedWhen = (SELECT min(CreatedWhen)
FROM SXAEDLocationAudit
WHERE ClientVisitGUID = tbadm.ClientVisitGUID
and ColumnName = tbadm.ColumnName
and ColumnNewValue in ('TBADM', 'Pre-Adm'))

-- Admission Time - when ADM was selected on Tracking Board

Left Outer Join sxaedlocationaudit adm
on adm.clientvisitguid = ela.clientvisitguid
and adm.columnName = 'STS'
and adm.ColumnNewValue = 'ADM'
and adm.CreatedWhen = (SELECT min(CreatedWhen)
FROM SXAEDLocationAudit
WHERE ClientVisitGUID = adm.ClientVisitGUID
and ColumnName = adm.ColumnName
and ColumnNewValue = 'ADM')

-- Delete is when they are removed from the tracking board, criteria when they leave ED for room

Left Outer Join sxaedlocationaudit del
on del.clientvisitguid = ela.clientvisitguid
and del.columnName = 'DELETE'
and del.CreatedWhen = (SELECT min(CreatedWhen)
FROM SXAEDLocationAudit
WHERE ClientVisitGUID = del.ClientVisitGUID
and ColumnName = 'Delete')

-- DSC to be discharged from the ED - Out the Door
left outer join sxaedlocationaudit dsc
on dsc.clientvisitguid = ela.clientvisitguid
and dsc.columnname = 'STS'
and dsc.ColumnNewValue = 'DSC'
and dsc.createdwhen = (SELECT Max(CreatedWhen)
FROM SXAEDLocationAudit
WHERE ClientVisitGUID = dsc.ClientVisitGUID
and ColumnName = dsc.ColumnName
and ColumnNewValue = 'DSC')

-- LWBS - left without being seen
Left Outer Join sxaedlocationaudit lwbs
on lwbs.clientvisitguid = ela.clientvisitguid
and lwbs.columnName = 'STS'
and lwbs.ColumnNewValue = 'LWBS'
and lwbs.CreatedWhen =(Select Max(createdwhen)
FROM SXAEDLocationAudit
WHERE ClientVisitGUID = lwbs.ClientVisitGUID
and ColumnName = lwbs.ColumnName
and ColumnNewValue = ('LWBS'))

-- Chief Complaint upon arrival
left outer join CV3HealthIssueDeclaration hid(nolock)
ON hid.ClientGUID = cv.ClientGUID
AND hid.chartguid = cv.chartguid
and hid.clientvisitguid = cv.guid
AND hid.TypeCode= 'chief complaint'
and hid.status = 'active'

Select * from @clientvisitlocation

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-11 : 16:03:54

Looks like you have invalid values in one of following columns:
cl.BirthMonthNum or cl.BirthDayNum or cl.BirthYearNum

Run the following on your production system to see where the invalid values are:

[CODE]
SELECT cl.guid, cl.BirthMonthNum, cl.BirthDayNum, cl.BirthYearNum FROM cv3client cl
WHERE (ISDATE(cast(cl.BirthMonthNum as nvarchar) + '/' + cast(cl.BirthDayNum as nvarchar) + '/' + cast(cl.BirthYearNum as nvarchar)) = 0
[/CODE]

mpayne2010
Starting Member

14 Posts

Posted - 2013-07-11 : 16:12:16

Im not getting any results when I run that

mpayne2010
Starting Member

14 Posts

Posted - 2013-07-11 : 16:13:37

When I change the isdate statement = 1 rather than 0 I am populating 517049 rows - so it looks like they are valid??

mpayne2010
Starting Member

14 Posts

Posted - 2013-07-11 : 16:33:17

If I comment out the
--Insertion_On_Board DateTime
and
--,Convert(Varchar(20),ins.createdwhen,120)

The report runs and ives me valid ages for all patients , when I uncomment that it blows up

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-11 : 21:04:57

You are not using Insertion_On_Board anywhere in the query other than in the table declaration so I don't think that is the culprit.
By process of elimination the only remaining suspect is ins.createdwhen,
try running the following query
[CODE]
SELECT * FROM sxaedlocationaudit ins WHERE ISDATE(ins.createdwhen) = 0
[/CODE]
to verify that the data is valid in that column

quote:
Originally posted by mpayne2010

If I comment out the
--Insertion_On_Board DateTime
and
--,Convert(Varchar(20),ins.createdwhen,120)

The report runs and ives me valid ages for all patients , when I uncomment that it blows up

mpayne2010
Starting Member

14 Posts

Posted - 2013-07-11 : 22:42:37

I ran SELECT * FROM sxaedlocationaudit ins WHERE ISDATE(ins.createdwhen) = 0 and it returned no results... and the query did complete successfully.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-12 : 00:18:39

It could be formatting issue, try converting the date to YYYYMMDD format.

SELECT cl.guid, cl.BirthMonthNum, cl.BirthDayNum, cl.BirthYearNum FROM cv3client cl
WHERE (ISDATE
(
CAST(cl.BirthYearNum AS varchar(4))
+LEFT('00'+CAST(c1.BirthMonthNum AS VARCHAR(2)),2)
+LEFT('00'+CAST(c1.BirthDayNum AS VARCHAR(2)),2)
) = 0

mpayne2010
Starting Member

14 Posts

Posted - 2013-07-12 : 14:30:39

When I run that I get 713,740 results. My apologies as I am fairly new to SQL. Did you mean to incorporate that into my actual query or just run the statement that you sent?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-12 : 17:21:44

mpayne, make some slight changes to the test query that Mumu posted:

SELECT TOP (1) cl.guid, cl.BirthMonthNum, cl.BirthDayNum, cl.BirthYearNum  FROM cv3client cl 
WHERE (ISDATE
(
 CAST(cl.BirthYearNum  AS varchar(4))
  +RIGHT('00'+CAST(c1.BirthMonthNum AS VARCHAR(2)),2)
  +RIGHT('00'+CAST(c1.BirthDayNum AS VARCHAR(2)),2)
 ) = 0

This is supposed to test if there are ANY rows in your table that cannot be converted to a date. This query will return one row, or zero rows. If it returns one row, that means, there are records in your table that cannot be converted to a date. Take a look at that result, or post it here.

If it does not return any rows at all, then, change your line that is currently this:

,DATEDIFF(yy, convert(datetime, cast(cl.BirthMonthNum as nvarchar) + '/' + cast(cl.BirthDayNum as nvarchar) + '/' + cast(cl.BirthYearNum as nvarchar)), cv.AdmitDtm)

Use this:

DATEDIFF(yy, convert(datetime, 
	CAST(cl.BirthYearNum  AS varchar(4))
	+RIGHT('00'+CAST(c1.BirthMonthNum AS VARCHAR(2)),2)
  +RIGHT('00'+CAST(c1.BirthDayNum AS VARCHAR(2)),2)), cv.AdmitDtm)

Here I am trying to use the YYYYMMDD format, which is the recommended format for date literals. See the reason here: http://myshallowsqlblog.wordpress.com/sql-dates-use-iso-8601-format/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources