casting to datetime

Question

here is mah query..

 

USE [BMS]

GO

/****** Object: StoredProcedure [dbo].[sp_SearchParticularsDetailsByPivot] Script Date: 18-07-2016 14:15:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[sp_SearchParticularsDetailsByPivot]

(

@fromdate datetime,

@todate datetime

)

as

begin

declare @expenseNames nvarchar(max)='';

--select @expenseNames ='[Building_EXPENSES], [eLECTRIC]'

--select @expenseNames= case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + ltrim(rtrim(Expenses_Name)) + ']'

--from expenses

(select @expenseNames +=

+'['+Expenses_Name+']'

+','

FROM dbo.Expenses)

set @expenseNames=(SELECT LEFT(@expenseNames, (LEN(@expenseNames)-1)))

declare @dynamicSQL nvarchar(max)='';

select @dynamicSQL =

'select * from

(select dbo.CreditDebit.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,

Balance from dbo.CreditDebit inner join

dbo.CreditExpenses on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID

inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID

where dbo.CreditDebit.TransactionDate between cast("'+@fromdate+'",datetime) and cast("'+@todate+'",datetime) )src

pivot

(

sum(Expenses)

for Expenses_Name In ('+@expenseNames+')

)as pvt'

EXEC (@dynamicSQL)

end

 

when i execute this query i got error as

Msg 241, Level 16, State 1, Procedure sp_SearchParticularsDetailsByPivot, Line 20

Conversion failed when converting date and/or time from character string.

 

 

Answer 1

Check below procedure

ALTER procedure [dbo].[sp_SearchParticularsDetailsByPivot]

(

@fromdate datetime = '2016-07-18',

@todate datetime='2016-07-18'

)

as

begin

declare @expenseNames nvarchar(max)='';

--select @expenseNames ='[Building_EXPENSES], [eLECTRIC]'

--select @expenseNames= case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + ltrim(rtrim(Expenses_Name)) + ']'

--from expenses

(select @expenseNames +=

+'['+Expenses_Name+']'

+','

FROM dbo.Expenses)

set @expenseNames=(SELECT LEFT(@expenseNames, (LEN(@expenseNames)-1)))

declare @dynamicSQL nvarchar(max)='';

print(@expenseNames)

select @dynamicSQL =

'select * from

(select dbo.CreditDebit.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,

Balance from dbo.CreditDebit inner join

dbo.CreditExpenses on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID

inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID

where DATEDIFF(day,'''+CONVERT(VARCHAR(10),@fromdate,111)+''',TransactionDate)>=0 and DATEDIFF(day,'''+Convert(VARCHAR(10),@todate,111)+''',TransactionDate)<=0

) src

pivot

(

sum(Expenses)

for Expenses_Name In ('+@expenseNames+')

)as pvt order by SLNO'

print @dynamicSQL

EXEC (@dynamicSQL)

end

Answer 2

If any answer is helpful to you then you need to accept it bro...

 

If you selected answer as a accepted then  any another guys have same problem then it helpfull to him. 

Answer 3

THANX KETAN...ITSB WORKING...

Answer 4

KETAN IT GIVE ERROR AS

Msg 156, Level 15, State 1, Line 27

Incorrect syntax near the keyword 'select'.

Answer 5

Yes sourabh you may also order by it. Check below snippet.

pivot

(

sum(Expenses)

for Expenses_Name In ('+@expenseNames+')

)as pvt order by SLNO'

Answer 6

hey ketan i have one more question when i use order by creditdebit.id desc its nos working can u please check it...

Answer 7

thanx ketan its working .I will change mah datepeicker style according to query

Answer 8

thanx dear its working.But its works only when i take it in the form of 'yyyy-MM-dd'.

What to do if it is in form of dd-MM-yy

Answer 9

Yes Sourabh I got and I have seen your table structure..

Use below procedure it surely working...

ALTER procedure [dbo].[sp_SearchParticularsDetailsByPivot]

(

@fromdate datetime = '2016-07-18',

@todate datetime='2016-07-18'

)

as

begin

declare @expenseNames nvarchar(max)='';

--select @expenseNames ='[Building_EXPENSES], [eLECTRIC]'

--select @expenseNames= case when datalength(@expenseNames) = 0 then '' else ', ' end + '[' + ltrim(rtrim(Expenses_Name)) + ']'

--from expenses

(select @expenseNames +=

+'['+Expenses_Name+']'

+','

FROM dbo.Expenses)

set @expenseNames=(SELECT LEFT(@expenseNames, (LEN(@expenseNames)-1)))

declare @dynamicSQL nvarchar(max)='';

print(@expenseNames)

select @dynamicSQL =

'select * from

(select dbo.CreditDebit.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,

Balance from dbo.CreditDebit inner join

dbo.CreditExpenses on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID

inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID

where DATEDIFF(day,'''+CONVERT(VARCHAR(10),@fromdate,111)+''',TransactionDate)>=0 and DATEDIFF(day,'''+Convert(VARCHAR(10),@todate,111)+''',TransactionDate)<=0 ) src

pivot

(

sum(Expenses)

for Expenses_Name In ('+@expenseNames+')

)as pvt'

print @dynamicSQL

EXEC (@dynamicSQL)

end

Answer 10

will u find mah attachment @ketan

Answer 11

here is mah scripts of both table and procedure....

please check this..

Answer 12

ya i am sending u all my procedure as well as table scripts

Answer 13

Please provide your table scrips.

Answer 14

its give he same error

Msg 102, Level 15, State 1, Procedure sp_SearchParticularsDetailsByPivot, Line 32

Incorrect syntax near ')'.

Answer 15

Try below procedure,

ALTER procedure [dbo].[sp_SearchParticularsDetailsByPivot]

(

@fromdate datetime,

@todate datetime

)

as

begin

declare @expenseNames nvarchar(max)='';

(select @expenseNames +=

+'['+Expenses_Name+']'

+','

FROM dbo.Expenses)

set @expenseNames=(SELECT LEFT(@expenseNames, (LEN(@expenseNames)-1)))

declare @dynamicSQL nvarchar(max)='';

print(@expenseNames)

select @dynamicSQL =

'select * from

(select dbo.CreditDebit.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,

Balance from dbo.CreditDebit inner join

dbo.CreditExpenses on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID

inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID

where dbo.CreditDebit.TransactionDate>='''+ Convert(varchar(20), @fromdate,111) + ''' and ' + 'dbo.CreditDebit.TransactionDate<='''+Convert(varchar(20), @todate,111) +''' ) src

pivot

(

sum(Expenses)

for Expenses_Name In ('+@expenseNames+')

)as pvt'

EXEC (@dynamicSQL)

end

Answer 16

Hi,

Brother please take a look at the query and try to identify the error.

Thanks.

:)

Answer 17

i run both query it will give error as

Msg 102, Level 15, State 1, Procedure sp_SearchParticularsDetailsByPivot, Line 32

Incorrect syntax near ')'.

Answer 18

Hi Sourabh,

Cast not contain comma(,) Check Cast method everywhere is in below format?

cast("'+@fromdate+'" as datetime)

Answer 19

Hi,

It seems you are not converting DateTime variable to NVarchar.

Just convert your @dateTime Variable to Convert(varchar(20),@@dateTime) and you wont get that error.

select @dynamicSQL =

'select * from

(select dbo.CreditDebit.ID AS SLNO, TransactionDate, Particular,Expenses_Name,Debit_Expenses,Credit_Expenses,Expenses,

Balance from dbo.CreditDebit inner join

dbo.CreditExpenses on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID

inner join dbo.Expenses on dbo.CreditExpenses.Expenses_ID=dbo.Expenses.ID

where dbo.CreditDebit.TransactionDate between '+Convert(Varchar(20),@fromdate)+'and '+ Convert(varchar(20),@todate)+') src

pivot

(

sum(Expenses)

for Expenses_Name In ('+@expenseNames+')

)as pvt'

I might mistaken the braces but you just convert the datetime variable to varchar type.

Hope it helps.

Thanks.

:)