How to retrieve data from database where date as input value to storedprocedure

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

Hi,
i am getting an error while writing a stored procedure that takes date as input value
and returns time in hours.i have used to extract for hours substring on a datetime column.
"error is cannot convert date/time from string"

the sample query is:

SQL

ALTER PROCEDURE [dbo].[Sp_GetTotalOrders]  
@DateFrom  NVARCHAR(50)
AS
Declare @curDate nvarchar(50);
BEGIN
set @curDate = @DateFrom + %;
    select count(a.Id) TotalCount, SUBSTRING(ShipDate,11, 3) ShipTime from
ShipmentDetail a inner join Items b on a.Id = b.ShipmentID 
where ShipDate like @curDate
Group by SUBSTRING(ShipDate,11, 3) order by ShipTime

Posted 6-Aug-12 1:58am

Madhugundi

Updated 6-Aug-12 2:08am

AshishChaudha

v2

Add a Solution

Comments

Santhosh Kumar Jayaraman 6-Aug-12 8:04am

what is the column type for shipdate? varchar or datetime?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Answer 1 · 2012-08-06T02:28:00

Solution 1

when comparing two fields datatype should be same
if 'Shipdate' having 'Date/Time' field then @DateFrom & @curDate should have same datatype

Happy Coding!
:)

Posted 6-Aug-12 2:28am

Aarti Meswania

Comments

Madhugundi 8-Aug-12 1:11am

Thanks

Aarti Meswania 8-Aug-12 1:12am

welcome :)

Santhosh Kumar Jayaraman · Answer 2 · 2012-08-06T02:29:00

I think your shipdate column is datetime datatype and not varchar..
So you have to convert.

SQL

ALTER PROCEDURE [dbo].[Sp_GetTotalOrders]  
@DateFrom  NVARCHAR(50)
AS
Declare @curDate nvarchar(50);
BEGIN
set @curDate = @DateFrom + %;
    select count(a.Id) TotalCount, Convert(Time,Shipdate) ShipTime from
ShipmentDetail a inner join Items b on a.Id = b.ShipmentID 
where CONVERT(DATE,shipdate,101) like @curDate
Group by Convert(Time,Shipdate) order by ShipTime

This will still give you error because of group by. You have to add columns in select statement to group by. Wht i tried above is getting time from datetime column where date is something.

Arun Vasu · Answer 3 · 2012-08-06T02:31:00

Hi AshishChaudha

You can't use datetime value for substring function. Substring only accept string value, so you need to covert datetime to string. for example

select substring(convert(nvarchar(20),ShipDate,20),12,8)

it will return 17:59:33.

If you have any query mail me.

Thanks and Regards
Arun vasu
CARE - IT
OTTAPALAM, KERALA.
arunsneha@outlook.com

How to retrieve data from database where date as input value to storedprocedure

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0