casting to datetime

sourabh choubey

10y1.2k

here is mah query..

USE [BMS]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER procedure [dbo].[sp_SearchParticularsDetailsByPivot]

(

@fromdate datetime,

@todate datetime

)

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.

19 Replies

Know the answer? Post it — somebody with the same question will find it here.

ketan borsdiya10y ago
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.
sourabh choubey10y ago
THANX KETAN...ITSB WORKING...
ketan borsdiya10y ago
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
sourabh choubey10y ago
KETAN IT GIVE ERROR AS
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'select'.
ketan borsdiya10y ago
Yes sourabh you may also order by it. Check below snippet.
pivot
(
sum(Expenses)
for Expenses_Name In ('+@expenseNames+')
)as pvt order by SLNO'
sourabh choubey10y ago
hey ketan i have one more question when i use order by creditdebit.id desc its nos working can u please check it...
sourabh choubey10y ago
thanx ketan its working .I will change mah datepeicker style according to query
sourabh choubey10y ago
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
ketan borsdiya10y ago
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
sourabh choubey10y ago
will u find mah attachment @ketan
sourabh choubey10y ago
here is mah scripts of both table and procedure....
please check this..
sourabh choubey10y ago
ya i am sending u all my procedure as well as table scripts
ketan borsdiya10y ago
Please provide your table scrips.
sourabh choubey10y ago
its give he same error
Msg 102, Level 15, State 1, Procedure sp_SearchParticularsDetailsByPivot, Line 32
Incorrect syntax near ')'.
ketan borsdiya10y ago
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
Vishal Jadav10y ago
Hi,
Brother please take a look at the query and try to identify the error.
Thanks.
:)
sourabh choubey10y ago
i run both query it will give error as
Msg 102, Level 15, State 1, Procedure sp_SearchParticularsDetailsByPivot, Line 32
Incorrect syntax near ')'.
ketan borsdiya10y ago
Hi Sourabh,
Cast not contain comma(,) Check Cast method everywhere is in below format?
cast("'+@fromdate+'" as datetime)
Vishal Jadav10y ago
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.
:)