SQL Server Performance

date time problem

Discussion in 'SQL Server 2005 General Developer Questions' started by isa, Mar 19, 2008.

  1. isa New Member

    Hello everyone, I m using SQL SERVER 2005, here is my query that retuns the FormsCount of last 7 days from the current date, but this
    query returns nothing, i think some time issues , in my table LastUpdate column datatype id datetime and it contains date with time
    , i m trying to get date without time by converting it into varchar, but stills returns nothing Kindly chk it whats
    wrong in this query?


    select distinct

    (select count(*) from TvsRecords where LastUpdate= convert(varchar(10),getdate(),101)) as '7',
    (select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -1, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '6',
    (select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -2, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '5',
    (select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -3, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '4',
    (select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -4, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '3',
    (select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -5, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '2',
    (select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -6, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '1'

    from TvsRecords


    plz reply me asap,
    Thanx in Advance.

    isa, Mar 19, 2008
    #2

  2. FrankKalis Moderator

    If your column "LastUpdate" contains the time portion, it doesn't yield you anything stripping the time off the GETDATE() value. Your comparison should look something like
    ...WHERE DATEADD(DAY, DATEDIFF(DAY, 0, LastUpdate), 0) = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)...
    But probably something like this will also work for you:
    DECLARE @t TABLE (d1 datetime)
    INSERT INTO @t SELECT GETDATE()
    INSERT INTO @t SELECT GETDATE()-0.5
    INSERT INTO @t SELECT GETDATE()-0.75
    INSERT INTO @t SELECT GETDATE()-1
    INSERT INTO @t SELECT GETDATE()-1.25
    INSERT INTO @t SELECT GETDATE()-3
    INSERT INTO @t SELECT GETDATE()-3
    INSERT INTO @t SELECT GETDATE()-8

    SELECT
    DATEADD(DAY, DATEDIFF(DAY, 0, d1), 0), COUNT(*)
    FROM
    @t
    WHERE
    d1 > = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -7)
    GROUP BY
    DATEADD(DAY, DATEDIFF(DAY, 0, d1), 0)
    ----------------------- -----------
    2008-03-16 00:00:00.000 2
    2008-03-18 00:00:00.000 4
    2008-03-19 00:00:00.000 1

    (3 row(s) affected)


    FrankKalis, Mar 19, 2008
    #3

Share This Page