SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Out-Of-Range Value error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mpayne2010
Starting Member

14 Posts

Posted - 07/11/2013 :  15:48:31  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/11/2013 :  15:51:48  Show Profile  Reply with Quote
the query you posted is truncated can you post the complete query
Go to Top of Page

mpayne2010
Starting Member

14 Posts

Posted - 07/11/2013 :  15:57:19  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/11/2013 :  16:03:54  Show Profile  Reply with Quote
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:


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

Edited by - MuMu88 on 07/11/2013 16:05:42
Go to Top of Page

mpayne2010
Starting Member

14 Posts

Posted - 07/11/2013 :  16:12:16  Show Profile  Reply with Quote
Im not getting any results when I run that
Go to Top of Page

mpayne2010
Starting Member

14 Posts

Posted - 07/11/2013 :  16:13:37  Show Profile  Reply with Quote
When I change the isdate statement = 1 rather than 0 I am populating 517049 rows - so it looks like they are valid??
Go to Top of Page

mpayne2010
Starting Member

14 Posts

Posted - 07/11/2013 :  16:33:17  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/11/2013 :  21:04:57  Show Profile  Reply with Quote
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

SELECT * FROM sxaedlocationaudit ins WHERE ISDATE(ins.createdwhen) = 0 

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


Go to Top of Page

mpayne2010
Starting Member

14 Posts

Posted - 07/11/2013 :  22:42:37  Show Profile  Reply with Quote
I ran SELECT * FROM sxaedlocationaudit ins WHERE ISDATE(ins.createdwhen) = 0 and it returned no results... and the query did complete successfully.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/12/2013 :  00:18:39  Show Profile  Reply with Quote
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
Go to Top of Page

mpayne2010
Starting Member

14 Posts

Posted - 07/12/2013 :  14:30:39  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 07/12/2013 :  17:21:44  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000