如何从日期为存储过程输入值的数据库中检索数据 [英] How to retrieve data from database where date as input value to storedprocedure

问题描述

我在编写将日期作为输入值的存储过程时遇到错误
并返回以小时为单位的时间.我曾经在datetime列上提取了小时的子字符串.
错误无法从字符串转换日期/时间"

示例查询是:

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:

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

推荐答案

比较两个字段的数据类型应相同
如果收货日期"字段具有日期/时间"字段，则@DateFrom和@curDate应该具有相同的数据类型

祝您编码愉快！
:)

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

Happy Coding!
:)

我认为您的shipdate列是datetime数据类型，而不是varchar..
因此，您必须进行转换.

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

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

由于分组依据，这仍然会给您错误.您必须在select语句中添加列以进行分组.我在上面尝试过的是从datetime列获取时间，而date是什么.

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.

嗨AshishChaudha

您不能将datetime值用于子字符串功能.子字符串仅接受字符串值，因此您需要将日期时间隐藏为字符串.例如

选择子字符串(convert(nvarchar(20)，ShipDate，20)，12,8)

它将返回17:59:33.

如果您有任何疑问，请给我发邮件.

谢谢与问候
阿伦·瓦苏(Arun vasu)
护理-IT
喀拉拉邦OTTAPALAM.
[e-mail]

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.
[e-mail]

这篇关于如何从日期为存储过程输入值的数据库中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！