MSG-242 err - 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

MSG-242 err

Author

Topic

sowmyav
Starting Member

34 Posts

Posted - 2009-01-16 : 02:05:43

Hi,
I got the error as follows

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
for the below sql
SELECT Cast((Substring(ARR.Calendar_Month_N,5,2) + '-01-' + Substring(ARR.Calendar_Month_N,1,4)) as DateTime) > (Select Max(DateAdd(Month,-12,Cast((Substring(ARR.Calendar_Month_N,5,2) + '-01-' + Substring(ARR.Calendar_Month_N,1,4)) as DateTime))))
FROM ADATA_200811 arr
as I want only the last 12 months of data.
This actually comes in the where condition,but as this is not working, I made as seperate select and cheking it now.
Could anyone please help me on this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-16 : 02:32:11

What datatype is Calendar_Month_N? Sample data?

DECLARE	@StartFrom DATETIME

SET	@StartFrom = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)

SELECT	*
FROM	ADATA_200811
WHERE	Calendar_Month_N >= @StartFrom

E 12°55'05.63"
N 56°04'39.26"

sowmyav
Starting Member

34 Posts

Posted - 2009-01-16 : 03:17:40

Thanks for the reply.
The mentioned above qry is working fine and fetching data.
I have one more doubt which is stated below.
datatype of Calendar_month is char(6) .

select arr.PRODUCT,arr.Calendar_Month_N,arr.fld_inv,
Sum(arr.Fld_Inv) OVER(PARTITION BY arr.Calendar_Month ) as cum_fld,
Sum(arr.Fld_Inv) OVER(PARTITION BY arr.Calendar_Month ) * arr.ARR_3MRA over(partition by arr.calendar_month,arr.product) as new,
arr.ARR_3MRA
from Data_200811 arr
JOIN Smry_200811 NTF
ON (ARR.Product=NTF.Product And ARR.Calendar_Month=NTF.Calendar_Month)
group by ARR.PRODUCT,arr.Calendar_Month_N,arr.Calendar_Month,arr.ARR_3MRA,arr.fld_inv
and here I need to fetch based on the above cast for 12 months data.
which should be like WHERE

Cast((Substring(Calendar_Month,5,2) + '-01-' + Substring(Calendar_Month,1,4)) as DateTime) >
(Select Max(DateAdd(Month,-12,Cast((Substring(Calendar_Month,5,2) + '-01-' + Substring(Calendar_Month,1,4)) as DateTime))).

AS I am not able to fetch the data for the above query and where i have done the mistake?I am getting err in

Sum(arr.Fld_Inv) OVER(PARTITION BY arr.Calendar_Month ) * arr.ARR_3MRA over(partition by arr.calendar_month,arr.product) as new which is
cum_fld * (basedon prod for that calendar month value of arr) should be multipled.

It would be great help for me!
Thanks is advance

sowmyav
Starting Member

34 Posts

Posted - 2009-01-16 : 03:37:15

Thanks.
please ignore the above qry.
The below is working and in that i need to fetch the data using the cast.Can anyone please help me on this

select arr.PRODUCT,arr.Calendar_Month_N,arr.fld_inv,
Sum(arr.Fld_Inv) OVER(PARTITION BY arr.Calendar_Month ) as cum_fld,
((arr.fld_inv)/Sum(arr.Fld_Inv) OVER(PARTITION BY arr.Calendar_Month )) * arr.ARR_3MRA as new,
arr.ARR_3MRA
from Data_200811 arr
JOIN Smry_200811 NTF
ON (ARR.Product=NTF.Product And ARR.Calendar_Month=NTF.Calendar_Month)
group by ARR.PRODUCT,arr.Calendar_Month_N,arr.Calendar_Month,arr.ARR_3MRA,arr.fld_inv

which is workiong fine.And as I need to fetch for 1 year data (the last 12 months of data) the following where clause that uses the 8 character form of the Calendar_Month field (yyyymm):
WHERE
Cast((Substring(Calendar_Month,5,2) + '-01-' + Substring(Calendar_Month,1,4)) as DateTime) >

(Select Max(DateAdd(Month,-12,Cast((Substring(Calendar_Month,5,2) + '-01-' + Substring(Calendar_Month,1,4)) as DateTime)))

AS this is used by group by funct, whether i should use having and followed by condition and this
or is ther any other method??

Thanks in adv

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources