Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Fredrik Esseen 610 posts 906 karma points
    Dec 02, 2010 @ 10:11
    Fredrik Esseen
    0

    Filter query by date

    Hi!

    Ive created a dashboard usercontrol that lists latest logged in members.

    Now i want to filter the result to display logins during two dates. The problem is that the filter is not working!

    I think that is has something to do with the date format.

    In the db the field is set to DateTime and displays the date as: 12/1/2010 9:58:01 AM.

    When I retrieve the date and displays it in my repeater its displayed as: 2010-12-01 09:58:01

    Ive tried to hardcode the sqlstring like this:

    string

     

    sqlstr = "SELECT * FROM MemberLog WHERE LoggedInDate >" + "12/1/2010" + " ORDER BY LoggedInDate DESC";

    But I get all the records.

    How can I achieve this?

  • Rich Green 2246 posts 4008 karma points
    Dec 02, 2010 @ 11:05
    Rich Green
    1

    Hey,

    This is almost certainly because you are mixing date formats, so your query actually returns everyone logged in after the 12 Jan 2010.

    Try something like this 

     sqlstr = "SELECT * FROM MemberLog WHERE LoggedInDate > CONVERT(DATETIME, '2010-12-01 00:00:00', 102) ORDER BY LoggedInDate DESC";

    Rich

  • Fredrik Esseen 610 posts 906 karma points
    Dec 03, 2010 @ 11:17
    Fredrik Esseen
    0

    Tried but got the error:

    Incorrect syntax near ' '.

    Is that depending on the datetime format on the server?

  • David Verberckmoes 46 posts 77 karma points
    Dec 03, 2010 @ 14:37
    David Verberckmoes
    0

    This always works in SQL Server:

    sqlstr = "SELECT * FROM MemberLog WHERE LoggedInDate > '20101201' ORDER BY LoggedInDate DESC";

    No hassle with converting, just use 'yyyyMMdd' format

     

    Another option is to use

    dateadd(d,-1,getdate())

    This returns loggedin users in the last 24hrs

     

  • Dan Diplo 1554 posts 6205 karma points MVP 6x c-trib
    Dec 03, 2010 @ 15:02
    Dan Diplo
    0

    You should also really be using the umbraco SqlHelper class to make parametrised queries.

    SqlHelper sqlH = DataLayerHelper.CreateSqlHelper(umbraco.GlobalSettings.DbDSN);

     

Please Sign in or register to post replies

Write your reply to:

Draft