I need help with this error I am getting

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

I need help with this error I am getting

Author

Topic

Trininole
Yak Posting Veteran

83 Posts

Posted - 2012-08-28 : 10:16:33

Error msg:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

Here is my query below:

----------------------------

select distinct
[Last Name]
,[First Name]
,[Registration Date]
,[Year and Quarter]
,left(integratedPlacement,5) nhPlxQtr
,[nhd]
,[WAGES]
,[integratedPlacement]
,right(integratedPlacement,2) placementtype
,nhdEmp =
case
when nhd is not null
and wages is null
then nh.emp_nme
else 'x'
end
,nhd_emp_addr_ln1 =
case
when nhd is not null
and wages is null
then nh.emp_addr_ln1
else 'x'
end
,nhd_emp_cty =
case
when nhd is not null
and wages is null
then nh.emp_cty
else 'x'
end
,nhd_emp_st =
case
when nhd is not null
and wages is null
then nh.emp_st
else 'x'
end
,emp_zip =
case
when nhd is not null
and wages is null
then nh.emp_zip
else 'x'
end

,[company]
,[Address]
,[City]
,[State]
,[Zip Code]
,[Zip + 4]
,[PHONE]
,[Industry Type]
,[OneStop]
,[dhmOffice]

from
(

select distinct

xx.[Last Name]
,xx.[First Name]
,xx.[Registration Date]
,xx.[Year and Quarter]
,convert(smalldatetime, convert(varchar(10),nh.nh_dte, 112)) nhd
,xx.[WAGES]
,integratedPlacement =
case
when xx.[Year and Quarter] is not null
then xx.[Year and Quarter] + 'ui'
when 1=1
and xx.[Year and Quarter] is null
and nh.ssn = xx.ssn
and convert(smalldatetime, convert(varchar(10),nh.nh_dte, 112))
>= xx.[Registration Date]
then
convert(varchar,datepart(year,nh.nh_dte)) +
convert(varchar,datepart(quarter,nh.nh_dte)) + 'nh'

when xx.[Year and Quarter] is null
and nh.ssn != xx.ssn
then Null

when xx.wages is null
and nh.ssn = xx.ssn
and convert(smalldatetime, convert(varchar(10),nh.nh_dte, 112))
< xx.[Registration Date]
then Null

else 'N'
end
,xx.[company]
,xx.[Address]
,xx.[City]
,xx.[State]
,xx.[Zip Code]
,xx.[Zip + 4]
,xx.[PHONE]
,xx.[Industry Type]
,xx.[OneStop]
,xx.[dhmOffice]
,xx.ssn
from
(
select distinct
rgn [rgn]
,x.uSSN ssn
,convert(varchar,appid) [appid]
,convert(varchar,usn) [usn]
,lname [Last Name]
,fname [First Name]
,regdte [Registration Date]
,convert(varchar,yrqtr) [Year and Quarter]
,convert(money,wages) [WAGES]
,primadd1 [company]
,primadd5 [Address]
,primcity [City]
,primstate [State]
,primzip [Zip Code]
,primzip4 [Zip + 4]
,phone [PHONE]
,indname [Industry Type]
,ofx_ofx [OneStop]
,dhmOffice

from
(
select de.*
,ui.*
--------------
,EMPDuringPartx =
case
when
(de.hssn = ui.ssn and (convert(int,ui.yrqtr) - convert(int,de.regYQi))= 1)
or (de.hssn = ui.ssn and (convert(int,ui.yrqtr) - convert(int,de.regYQi))= 2)
or (de.hssn = ui.ssn and (convert(int,ui.yrqtr) - convert(int,de.regYQi))= 3)
or (de.hssn = ui.ssn and (convert(int,ui.yrqtr) - convert(int,de.regYQi))= 4)
then 'Y'
else 'N'
end
----------
,EMPQ1aftExitYN =
case
when (de.hssn = ui.ssn and (convert(int,ui.yrqtr) - convert(int,de.xdtYQi))= 1)
then 'Y'
else 'N'
end

from
ospr_workarea.dbo.dhmEFM de
left outer join ospr_workarea.dbo.roger_ui ui

on (de.hssn = ui.ssn and (ui.YRQTR >= de.regYQ or (ui.YRQTR >= de.appYQ ))
)
where 1=1

) x
where 1=1

and xdtYQ is not null ---exiters only

) xx
left outer join ospr_workarea.dbo.mmr_dor_new_hire nh
on (
nh.ssn = xx.ssn
and xx.[Year and Quarter] is null
and left(nh.nh_dte,2) = '20'
and convert(smalldatetime, convert(varchar(10),nh.nh_dte, 112))
>= xx.[Registration Date]
)

) xx1
left outer join ospr_workarea.dbo.mmr_dor_new_hire nh
on (
nh.ssn = xx1.ssn
and left(nh.nh_dte,2) = '20'
and convert(smalldatetime, convert(varchar(10),nh.nh_dte, 112))
>= xx1.[Registration Date]
and xx1.nhd is not null
)

Roger DeFour

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-28 : 10:18:31

the error must be coming from "convert(smalldatetime, convert(varchar(10),nh.nh_dte, 112))"

what is the data type for column nh.nh_dte ?

KH
[spoiler]Time is always against us[/spoiler]

Trininole
Yak Posting Veteran

83 Posts

Posted - 2012-08-28 : 10:42:12

The data type is varchar(8),null

Roger DeFour

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-28 : 10:50:17

Examine the data in each of the columns that you are converting to smalldatetime to see if they are valid dates. For example, to examine nh_dte column in the table ospr_workarea.dbo.mmr_dor_new_hire, use this query:

SELECT
	*
FROM
	ospr_workarea.dbo.mmr_dor_new_hire
WHERE
	ISDATE(nh_dte) = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources