Press Ctrl / CMD + C to copy this to your clipboard.
This post will be reported to the moderators as potential spam to be looked at
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";
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?
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
Tried but got the error:
Incorrect syntax near ' '.
Is that depending on the datetime format on the server?
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
You should also really be using the umbraco SqlHelper class to make parametrised queries.
SqlHelper sqlH = DataLayerHelper.CreateSqlHelper(umbraco.GlobalSettings.DbDSN);
is working on a reply...
Write your reply to:
Upload image
Image will be uploaded when post is submitted
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?
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
Rich
Tried but got the error:
Incorrect syntax near ' '.
Is that depending on the datetime format on the server?
This always works in SQL Server:
No hassle with converting, just use 'yyyyMMdd' format
Another option is to use
This returns loggedin users in the last 24hrs
You should also really be using the umbraco SqlHelper class to make parametrised queries.
is working on a reply...