在SQL中将datetime转换为varchar [英] Convert datetime to varchar in sql

查看:100
本文介绍了在SQL中将datetime转换为varchar的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

你好朋友,

我有一个存储过程,其中我要传递字符串("2011-12-23 00:00:00"),但是我要与之进行比较的列具有DateTime数据类型.

我需要转换其中任何一个.
所以我尝试了-CONVERT(DATETIME,@ date,120),但是由于从字符串转换日期时间时转换失败,因此出现错误".

查询出了什么问题?

Hello Friends,

I have a stored procedure in which I am passing string ("2011-12-23 00:00:00"), however the column with which i am trying to compare it with has DateTime datatype.

I need to convert either of them.
So I tried -- CONVERT(DATETIME,@date,120) but I am getting error as "Conversion failed when converting datetime from character string."

whats wrong with the query?

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(  
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	VARCHAR=NULL,
	 @EndDate VARCHAR=NULL
) 
AS  

BEGIN  
	SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
	FROM Requests
	WHERE
		EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL THEN RequestStatusId ELSE @RequestStatusId END)
		AND AddedOn	LIKE (CASE WHEN CONVERT(DATETIME,@StartDate,120) IS NULL THEN AddedOn ELSE CONVERT(DATETIME,@StartDate,120) END)	
END

推荐答案

IMO最好的选择是将datetime数据类型用于您的参数.这样,您无需在db进行任何转换.客户端仍可能需要进行转换,但是可以适当考虑不同的语言环境.这样的过程可能是:
IMO the best option would be to use datetime data type for your parameters. This way you wouldn''t need to do any conversions at db. Conversions may still be needed at client side but then different locales can be properly taken into account. So the procedure could be:
ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(  
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	datetime=NULL,
	 @EndDate datetime=NULL
) 
AS 
...


使用此代码:

CONVERT(VARCHAR(20),@ date,107)AS日期

问候
USE this code:

CONVERT(VARCHAR(20),@date,107) AS Date

Regards


尝试一下,因为日期时间太长..

Try this, it because of size of datetime..

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(  
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	VARCHAR=NULL,
	 @EndDate VARCHAR=NULL
) 
AS  
 
BEGIN  
	SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
	FROM Requests
	WHERE
		EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL THEN RequestStatusId ELSE @RequestStatusId END)
		AND AddedOn	BETWEEN 
		CONVERT(VARCHAR(50),@StartDate,113)+''00:00:00'',120) 
		AND
		CONVERT(VARCHAR(50),@EndDate,113)+''23:59:59'',120)	
END


这篇关于在SQL中将datetime转换为varchar的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆