Search code examples
sqlsql-serverdatetimevarchar

how to date format 103 to group by using where clause with string format (varchar(103),...,10)

Im having trouble on a query with a datetime field.

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.

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

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

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.

Solution

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

    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))

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

    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.

    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

    Or

    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).