日期超出范围但仅在存储过程中调用
我试图在我的查询中将DATETIME
字段转换为dd-mm-yyyy
格式。日期超出范围但仅在存储过程中调用
当我在SSMS中运行以下任一行时,查询将成功执行,并以正确的格式获取我的日期。
CONVERT(VARCHAR(30), CONVERT(DATETIME, f.Created, 101), 103) as [Created]
CONVERT(VARCHAR, f.created, 105) as [Created]
f.Created
是一个DATETIME列
但是,如果我尝试运行它作为一个存储过程中的查询的一部分,我得到一个错误:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
在存储过程,我试过将语言设置为British
,日期格式为dmy
,但我仍然遇到超出范围的错误。如果我删除这些行或通常只选择f.Created
字段,它就可以工作。
什么问题?
编辑:
查询以运行SP
DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT, @query = N'
SELECT top 100 * from
(
select
c.clno + ''.'' + f.fileno as [Number]
,c.clName as [Client Name]
,f.fileDesc as [File name]
,CONVERT(VARCHAR(255), f.created, 105) as Created_ddmmyyyy
--Or either of these:
--CONVERT(VARCHAR(30), CONVERT(DATETIME, f.Created, 101), 103) as [Created]
--CONVERT(VARCHAR, f.created, 105) as [Created]
from config.dbfile f
join config.dbclient c on c.clid = f.clid
) x
where x.Department = ''Import'' and Type = ''Import''
and
x.Created_ddmmyyyy Between DATEADD(m, -2, GETDATE()) and GETDATE()
', @orderBy = '';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Outlook',
@recipients = '[email protected];',
@subject = 'subject of email',
@body = @html,
@body_format = 'HTML',
@query_no_truncate = 1,
@attach_query_result_as_file = 0;
SP:
/****** Object: StoredProcedure [dbo].[spQueryToHtmlTable] Script Date: 10/16/2017 11:47:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Description: Turns a query into a formatted HTML table. Useful for emails.
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable]
(
@query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
@orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
@html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN
SET NOCOUNT ON;
IF @orderBy IS NULL BEGIN
SET @orderBy = ''
END
SET @orderBy = REPLACE(@orderBy, '''', '''''');
DECLARE @realQuery nvarchar(MAX) = '
DECLARE @headerRow nvarchar(MAX);
DECLARE @cols nvarchar(MAX);
SELECT * INTO #dynSql FROM (' + @query + ') sub;
SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
FROM tempdb.sys.columns
WHERE object_id = object_id(''tempdb..#dynSql'')
ORDER BY column_id;
SET @cols = ''SET @html = CAST((SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''
EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @[email protected] OUTPUT
SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>''
FROM tempdb.sys.columns
WHERE object_id = object_id(''tempdb..#dynSql'')
ORDER BY column_id;
SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';
SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';
';
EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @[email protected] OUTPUT
END
GO
典型数据在f.created柱
2002-11-05 00:00:00.000
2003-12-15 00:00:00.000
2002-11-05 00:00:00.000
2002-11-05 00:00:00.000
2002-11-06 00:00:00.000
为F预期结果。创建了列
05-11-2002
15-12-2003
05-11-2002
05-11-2002
06-11-2002
我将很高兴与DD-MM-YY/YYYY的任何变化
的错误,然后你试图比较与其他两个日期(BETWEEN)
该字符串只动子查询中这种情况下,用在何处,并在SELECT转换的原始DATETIME列...。
其实为什么你甚至可以使用子查询:
@query = N'
select top 100
c.clno + ''.'' + f.fileno as [Number]
,c.clName as [Client Name]
,f.fileDesc as [File name]
,CONVERT(VARCHAR(255), f.created, 105) as Created
from config.dbfile f
join config.dbclient c on c.clid = f.clid
where Department = ''Import''
and Type = ''Import''
and f.Created Between DATEADD(m, -2, GETDATE()) and GETDATE()'
或有两列 - 一个原始的DATETIME在何处,其他转换为VARCHAR展现给客户使用。
我有一个子查询,因为有一个函数返回的两个别名列(不包括在我的文章中,为了避免混淆),它不能在'where子句中使用,但可以在子查询中 –
@MrJF OK,如果需要的话保留子查询,但逻辑保持不变,在'datetime'列上使用'where'条件..你能从这里得到你的解决方案吗? –
是的,这实际上工作。谢谢! –
正确的方法是简单地:
CONVERT(VARCHAR(255), f.created, 105) as [Created]
你应该总是包括长度与SQL字符串类型服务器。 但是,这不会解决您的问题。一个问题可能是命名约定。我不知道你的存储过程是什么样子,但你可以尝试:
CONVERT(VARCHAR(255), f.created, 105) as Created_ddmmyyyy
这样一来,两者不会混淆。如果Created_ddmmyyyy
然后被用于某种目的,您的查询可能会尝试将其转换回日期/时间 - 使用不同的格式。
您不应该得到将日期/时间转换为字符串的超范围错误。
我已经尝试过两个例子,但都没有成功。我犯了同样的错误。我会更新我的帖子的所有细节 –
@MrJF。 。 。你错过了这一点。使用'created'来比较日期/时间值。这是一个日期/时间。用它! –
如果已经使用日期时间格式,请不要转换f.created列。 dateadd()返回一个日期时间,与您的列的类型完全匹配。
SELECT top 100 * from
(
select
c.clno + '.' + f.fileno as [Number]
,c.clName as [Client Name]
,f.fileDesc as [File name]
,f.created
--,CONVERT(VARCHAR(255), f.created, 105) as Created_ddmmyyyy
from config.dbfile f
join config.dbclient c on c.clid = f.clid
) x
where x.Department = 'Import' and Type = 'Import'
and x.Created >= DATEADD(m, -2, GETDATE())
and x.Created < GETDATE()
我能想到的,为什么你已经添加了转换的唯一原因,是因为你读的地方,GETDATE()包括时间成分,你想要的结果昨天零下2个月返回直到今天,而不是“现在”减2个月直到“现在”(即包括时间)。如果是这样的话,你会被互联网上发现的许多不正确的例子之一所误导。忘掉转换成字符串,只需使用DATEADD)的智能组合()和DATEDIFF(你会得到你所需要的东西,没有你现在所遇到的国际日期格式恶梦,作为一个额外的好处:它的速度更快。如果要充分了解,请阅读:Dwain Camps' blog post called "Manipulating Dates and Times in T-SQL"。但是这里有一个简单的例子,它实现了日期截断来摆脱getdate()返回的时间组件。
SELECT top 100 * from
(
select
c.clno + '.' + f.fileno as [Number]
,c.clName as [Client Name]
,f.fileDesc as [File name]
,f.created
--,CONVERT(VARCHAR(255), f.created, 105) as Created_ddmmyyyy
from config.dbfile f
join config.dbclient c on c.clid = f.clid
) x
where x.Department = 'Import' and Type = 'Import'
and x.Created >= DATEADD(m, -2, dateadd(d, datediff(d, 0, GETDATE()), 0))
and x.Created < dateadd(d, datediff(d, 0, GETDATE()), 0)
还请注意使用> =和<代替间,在我的例子。之间既包括起始值和结束值,因此你可以不正确地包括具有00的时间成分行:00:00.000,如果你代替=>和<之间使用。正确
select convert(varchar(10), getdate(), 120)
至于另外再加上,它甚至各种各样:
使用GET格式,使得它不能被错误地解释日期字符串,格式为YYYY-MM-DD格式!在转换和格式化参数的全部信息可以在SQL Server的书籍在网上找到:在第一查询条件
WHERE
x.Created_ddmmyyyy Between DATEADD(m, -2, GETDATE()) and GETDATE()
,因为你已经将您DATETIME字符串出现Books online
感谢您的详细解答。我会阅读你提到的问题,因为这不是我第一次遇到日期问题!我转换'f.created'字段的原因是以更具可读性的格式显示它,因为查询结果将通过电子邮件发送,而SQL格式似乎没有为人类设计。当我运行你的版本时,电子邮件完全空白。 –
:-)我刚刚意识到这一点,并已在我的原始答案中添加了一条简短评论。 – rrozema
是什么类型'f.created'?你为什么要把它转换两次?你能提供一些样本数据和预期结果吗? –
DATETIME。我已更新我的帖子。 –