如何通过使用where子句与字符串格式（varchar（103），...，10）将103格式化为分组

Im having trouble on a query with a datetime field.

我在使用datetime字段的查询时遇到问题。

Its because i convert the datetime field to -varchar(10),..,103- so i can apply a where clause with date field on 103 format instead of datetime but then when i use the where clause it doesnt show results or group the way i need to, because the datetime field was converted to string.

因为我将datetime字段转换为-varchar(10),..,103-所以我可以在103格式而不是datetime上应用带有日期字段的where子句但是当我使用where子句时它不显示结果或分组我需要的方式,因为datetime字段已转换为字符串。

Here is a simplified query for the example:

以下是该示例的简化查询:

select ddate,SUM(ntotal) as Income from Inc_orders    
where nbranch=2 
and convert(varchar(10),ddate,103)                                   
between '01/06/2010' and '31/06/2010'   
group by convert(varchar(10),ddate,103)  
order by ddfecha desc

ddate is the datetime field
ntotal is integer
nbranch is foreign key

ddate是datetime字段,ntotal是整数nbranch是外键

Then what happens is that i get results from another 103 date range

然后会发生的是我从另一个103日期范围得到结果

01/10/2009  4447.0000    
02/01/2010  26267.8000    
02/02/2010  20498.0000    
02/04/2010  22565.1000   
02/05/2010  20539.0000    
02/11/2010  33934.3000     
02/12/2009  33587.4000

What i pretend to look it like is :

我假装看起来像是:

01/06/2010  29327.7000       
02/06/2010  31170.4000     
03/06/2010  37737.7000     
04/06/2010  25109.6000     
06/06/2010  20819.7000     
10/06/2010  44703.9000     
14/06/2010  21755.1000     
15/06/2010  39369.3000    
05/06/2010  29552.2000    
07/06/2010  35305.9000    
08/06/2010  30628.6000    
..........     
31/06/2010  18677.6000

A solution is not using datepart, month, or year functions because i need the parameter to look like a calendar to apply a datetimepicker calentad combo object on it.

解决方案不使用datepart,month或year函数,因为我需要将参数看作日历以在其上应用datetimepicker calentad组合对象。

4 个解决方案

#1

Do not use CONVERT(VARCHAR, DateField, 103) to remove the time from DATETIME it is inefficient and also causes problems when sorting.

不要使用CONVERT(VARCHAR,DateField,103)从DATETIME中删除时间效率低,并且在排序时也会导致问题。

Depending on the version of SQL-Server you are using there are 2 options that are usually regarded as the best. For SQL-Server 2008 and upwards use CAST(DateField AS DATE), for previous versions use DATEADD(DAY, 0, DATEDIFF(DAY, 0, DateField))

根据您使用的SQL-Server的版本,有两个选项通常被认为是最好的。对于SQL-Server 2008及更高版本,使用CAST(DateField AS DATE),对于以前的版本,使用DATEADD(DAY,0,DATEDIFF(DAY,0,DateField))

Because you are converting Ddate to a VARCHAR in this line:

因为您要将Ddate转换为此行中的VARCHAR:

convert(varchar(10),ddate,103) between '01/06/2010' and '31/06/2010'

you are removing the implicit conversion of '01/06/2010' and '31/06/2010' to dates. This means '02/01/2000' is greater than '01/01/2012' because you are comparing strings not dates. If you remove the time from Ddate and keep the expression in a date(time) format, '01/06/2010' and '31/06/2010' are implicitly converted to dates.

您要删除'01 / 06/2010'和'31 / 06/2010'到日期的隐式转换。这意味着'02 / 01/2000'大于'01 / 01/2012',因为您正在比较字符串而不是日期。如果从Ddate中删除时间并将表达式保持为日期(时间)格式,则“01/06/2010”和“31/06/2010”将隐式转换为日期。

To illustrate this simply you can run this simple query:

为了说明这一点,您可以运行这个简单的查询:

SELECT  CASE WHEN '02/06/2000' BETWEEN '01/06/2012' AND '03/06/2012' THEN 1 ELSE 0 END [String Comparison],
        CASE WHEN CONVERT(DATETIME, '02/06/2000') BETWEEN '01/06/2012' AND '03/06/2012' THEN 1 ELSE 0 END [Date Comparison]

So your query would end up something like this:

所以你的查询最终会是这样的:

SET DATEFORMAT DMY

SELECT  CAST(DDate AS DATE) Ddate,
        SUM(ntotal) as Income 
FROM    Inc_orders    
WHERE   nbranch=2 
AND     CAST(DDate AS DATE) BETWEEN '01/06/2010' AND '31/06/2010'   
GROUP BY CAST(DDate AS DATE)
ORDER BY DDate

SELECT  DATEADD(DAY, 0, DATEDIFF(DAY, 0, DDate)) Ddate,
        SUM(ntotal) as Income 
FROM    Inc_orders    
WHERE   nbranch=2 
AND     DATEADD(DAY, 0, DATEDIFF(DAY, 0, DDate)) BETWEEN '01/06/2010' AND '31/06/2010'   
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, DDate))
ORDER BY DDate

ADDENDUM

I am not sure if Ddate contains a time, so using the above to remove the time may not be relevant, however the part about comparing strings in the where clause remains relevant. In addition there are very few occassions when it should be necessary to present your date to your application in string format. It would be better to keep the date as a date and format it within you application layer (whatever this may be).

我不确定Ddate是否包含时间,因此使用上述方法删除时间可能不相关,但是有关比较where子句中的字符串的部分仍然相关。此外,只有很少的时候才需要以字符串格式向您的应用程序提供日期。最好将日期保留为日期并将其格式化在您的应用程序层中(无论这可能是什么)。

4 个解决方案

#1

更多相关文章

随机推荐