DATEADD Function? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

DATEADD Function?

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 02:37:34

How can i add DATE??
I want fields where d+m< GivenDate
In this case,sDate is datetime and nMonth is Integer in table.
Plz help me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 02:39:57

[code]
where sDate < dateadd(month, -nMonth, GivenDate)
[/code]

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 03:12:49

this is procedure
CREATE PROCEDURE [dbo].[d_GetCustInfo]
@ddate as datetime
AS
select c.CustomerID as cid,c.Name as cname,c.TempName as contact,c.StartDate as sdate,c.NumMth as nm,b.PRODUCT_DESC as pdesc,s.Name as staff
from Customer c
inner join BillRecords b on c.CustomerID=b.CUSTOMERID
inner join SalesRep s on c.SalesRep=s.SalesRepID
where c.StartDate < dateadd(month, -c.NumMth, @ddate)
GO

I've got error like:
Syntax error converting the varchar value '/*' to a column of data type int.

quote:
Originally posted by khtan

where sDate < dateadd(month, -nMonth, GivenDate)

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 03:14:33

what's the data type for NumMth ? varchar ? use convert() to convert to integer

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 03:17:57

NumMth is Integer in table..

quote:
Originally posted by khtan

what's the data type for NumMth ? varchar ? use convert() to convert to integer

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

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 03:20:30

what about your customerID and salesrepID. are you joining varchar values to integers?

Em

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 03:23:27

No, these are also Integers..

quote:
Originally posted by elancaster

what about your customerID and salesrepID. are you joining varchar values to integers?

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 03:29:12

where did the error comes from ? When executing the SP ?

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 03:29:14

Plz can u tell me what's wrong??

Thanks

quote:
Originally posted by elancaster

what about your customerID and salesrepID. are you joining varchar values to integers?

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 03:29:47

Please post your table structure

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 03:32:51

Customer(CustomerID-int,SaleRep-int,Startdate-datetime,NumMth-int...)
SalesRep(SalesRepID-int,....)
BillRecords(CustomerID-int,..)

quote:
Originally posted by khtan

Please post your table structure

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

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 03:35:25

what are you passing in as the date variable?

Em

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 03:36:28

i pass datetime value like -7/20/2008

quote:
Originally posted by elancaster

what are you passing in as the date variable?

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 03:43:47

in single quote ?


exec d_GetCustInfo @ddate = '20080720'

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 03:46:23

i pass it from code like:
Dim pdate As New SqlParameter("@ddate", SqlDbType.DateTime)
pdate.Value = CType(txtDate.Text, DateTime)
cmd.Parameters.Add(pdate)

quote:
Originally posted by khtan

in single quote ?

exec d_GetCustInfo @ddate = '20080720'

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 03:50:08

try running this in Query Analyzer or via SSMS

exec d_GetCustInfo @ddate = '20080720'

See any error returns ?

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 03:55:00

sorry for wrong message.Actually datatype of SalesrepID and Salesrep
is AadSalesRepID (varchar)
So,how can i do??
Thanks.

quote:
Originally posted by khtan

try running this in Query Analyzer or via SSMS
exec d_GetCustInfo @ddate = '20080720'

See any error returns ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 03:58:27

in all the tables ? or one of them is integer ?
check that the data type of the column in the JOIN are of same type

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 04:00:16

ya
same for both talble..

quote:
Originally posted by khtan

in all the tables ? or one of them is integer ?
check that the data type of the column in the JOIN are of same type

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 04:04:11

if that's the case . .then no reason for the conversion error.

are you sure the error is coming from this stored procedure ? Try running the SP in Query Analyzer or SSMS Query Window

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-07-17 : 04:06:48

Datatype of CustomerID in BillRecords is varchar,and that of Customer is int
But i cannot change in design view,so how can i convert varchar to int in join.
I'm so sorry for my wrong things.
Thanks for ur patient replies.

quote:
Originally posted by kwikwisi

ya
same for both talble..

quote:
Originally posted by khtan

in all the tables ? or one of them is integer ?
check that the data type of the column in the JOIN are of same type

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources