Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: date query problem

    why aint this return the records for current day if al FROM and TO variabels is set to current date?

    SELECT TOP 20 VLF_VirusName, COUNT(VLF_VirusName) AS number
    FROM tb_AVVirusLog
    WHERE (CLF_ComputerName LIKE N'%') AND (VLF_InfectionDestination LIKE N'%') AND (VLF_InfectionSource LIKE N'%') AND (CONVERT(varchar,
    CLF_LogGenerationTime, 120) BETWEEN @from_year + @from_month + @from_day AND @to_year + @to_month + @to_day)
    GROUP BY VLF_VirusName
    ORDER BY COUNT(VLF_VirusName) DESC


    This works (it gives me current days records), but i want to be able to specify end date instead of current date.

    SELECT TOP 20 VLF_VirusName, COUNT(VLF_VirusName) AS number
    FROM tb_AVVirusLog
    WHERE (CLF_ComputerName LIKE N'%') AND (VLF_InfectionDestination LIKE N'%') AND (VLF_InfectionSource LIKE N'%') AND (CONVERT(varchar,
    CLF_LogGenerationTime, 120) BETWEEN @from_year + @from_month + @from_day AND CONVERT(varchar, GETDATE(), 120))
    GROUP BY VLF_VirusName
    ORDER BY COUNT(VLF_VirusName) DESC

  2. #2
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    You should "Cast" the @from_year, @from_Month, @from_day, etc variables as char or carchar, if not the "@from_year + @from_month + @from_day" contruct will simply add the _integers_ contained in those variables.

    So, I would replace the "@from_year + @from_month + @from_day"

    with

    "cast(@from_year as char(4)) + '-' + cast(@from_month as char(2)) + '-' + cast(@from_day as char(2)"

    you should do similar for the @to_ variables...

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by mrpcguy
    why aint this return the records for current day if al FROM and TO variabels is set to current date?
    Oh my.

    Post the DDL of your table, and why are your input date values broken up by year/month/day?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2004
    Posts
    162
    thx for help.
    its not broken up in table its only broken up in query so that users can put in these values in a form. maybe not the correct way to do it but it works for me

    and what u mean by DDL?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Perhaps this forum would be more helpful:

    http://rinkworks.com/dialect/dialect...w.dbforums.com
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2004
    Posts
    162
    ye that was helpful and creative...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •