Post reply

BETWEEN returns no column

  • August 7, 2009 at 6:02 am

    #203544

    Hi,

    i have a query like

    select count(*),date from tbl1

    where date between '2009/05/01' and '2009/05/31'

    group by date

    but when ever there is no data for a particular date that record is just ignored in the output

    i want to get either null or '0' for those columns also

    hw can i do it

    thanks,

    regards

    viji

  • August 7, 2009 at 6:07 am

    #1035915

    where date between '2009/05/01' and '2009/05/31' OR date is null

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • August 7, 2009 at 6:17 am

    #1035918

    Hi Bob Hovious,

    thanks for ur reply.

    but it ddn't work 🙁

    SELECT CONVERT(VARCHAR ,createddate , 111) DATE ,COUNT (*) COUNT

    FROM tro WHERE (CONVERT(VARCHAR ,createddate ,111)

    BETWEEN '2009/02/01' AND '2009/03/31' ) or CONVERT(VARCHAR ,createddate , 111) is null GROUP BY CONVERT(VARCHAR ,

    createddate ,111) ORDER BY CONVERT(VARCHAR ,createddate , 111)

    above is the code which i'm trying

    thanks

    regards

    viji

  • August 7, 2009 at 6:24 am

    #1035922

    Even i've tried with

    SELECT CONVERT(VARCHAR ,createddate , 111) DATE , CASE WHEN COUNT (*) IS NULL THEN 0 ELSE COUNT(*) END COUNT

    FROM tro WHERE CONVERT(VARCHAR ,createddate ,111) BETWEEN '2009/02/01' AND '2009/03/31'or createddate is null GROUP BY CONVERT(VARCHAR ,

    createddate ,111) ORDER BY CONVERT(VARCHAR ,createddate , 111)

    SELECT CONVERT(VARCHAR ,createddate , 111) DATE , isnull(COUNT (*),0) COUNT

    FROM tro WHERE CONVERT(VARCHAR ,createddate ,111) BETWEEN '2009/02/01' AND '2009/02/07'or createddate is null GROUP BY CONVERT(VARCHAR ,

    createddate ,111) ORDER BY CONVERT(VARCHAR ,createddate , 111)

    both not getting me the result.

    --viji

  • August 7, 2009 at 8:41 am

    #1036047

    Try the universal data format:

    SELECT count(*)

    ,[date]

    FROM tbl1

    WHERE [date] BETWEEN '20090501' AND '20090531 23:59:59.997'

    GROUP BY [date]

  • August 7, 2009 at 10:16 am

    #1036127

    Please post up a script to create a sample table with your date column, using the same datatype you are using in your production files. Populate it with some dates, including the dates which are giving you problems. You will find an example of the preferred format here[/url].

    I understood your question to be concerned only with nulls not being included, but he is right. Using BETWEEN with date/timestamps causes problems, because any date string which doesn't specify time indicates midnight at the start of that day. My preference is to always say: where date >= '1/1/2009' and date < '2/1/2009'

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply