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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

549 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

549 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

549 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

549 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

3873 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  
 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.09 seconds. Powered By: Snitz Forums 2000