Solved

SQL Query to locate all records in DB for today

Posted on 2007-12-06
3
179 Views
Last Modified: 2010-04-21
I am looking to create a SQL Query for my DotNetNuke site through SQL Report Server for all site log activity for today.  Here's what I've got so far:

SELECT     dnn_Portals.PortalName, dnn_SiteLog.DateTime, dnn_Users.Username, dnn_Users.FirstName, dnn_Users.LastName,
                      dnn_Tabs.TabName AS 'Resource Accessed'
FROM         dnn_Users INNER JOIN
                      dnn_SiteLog ON dnn_Users.UserID = dnn_SiteLog.UserId INNER JOIN
                      dnn_Portals ON dnn_SiteLog.PortalId = dnn_Portals.PortalID INNER JOIN
                      dnn_Tabs ON dnn_Portals.PortalID = dnn_Tabs.PortalID
WHERE     (dnn_Portals.PortalID = '0') AND (dnn_SiteLog.DateTime >= CONVERT(datetime, '12/6/2007 12:00:00 AM', 120)) AND
                      (dnn_SiteLog.DateTime < DATEADD(day, 1, CONVERT(datetime, '12/6/2007 11:59:59 PM', 120)))
GROUP BY dnn_Portals.PortalName, dnn_SiteLog.DateTime, dnn_Users.Username, dnn_Users.FirstName, dnn_Users.LastName, dnn_Tabs.TabName

As you can see, I've hard coded today's date, and this is working.  Now, I need to replace the hard coded date with some sort of today function but not sure what that is.

TIA for any help!
0
Comment
Question by:dstjohnjr
3 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20424780
SELECT     dnn_Portals.PortalName, dnn_SiteLog.DateTime, dnn_Users.Username, dnn_Users.FirstName, dnn_Users.LastName,
                      dnn_Tabs.TabName AS 'Resource Accessed'
FROM         dnn_Users INNER JOIN
                      dnn_SiteLog ON dnn_Users.UserID = dnn_SiteLog.UserId INNER JOIN
                      dnn_Portals ON dnn_SiteLog.PortalId = dnn_Portals.PortalID INNER JOIN
                      dnn_Tabs ON dnn_Portals.PortalID = dnn_Tabs.PortalID
WHERE     (dnn_Portals.PortalID = '0') AND (dnn_SiteLog.DateTime >= CONVERT(DATETIME,CONVERT(Varchar,Getdate(),102)) ) AND
                      (dnn_SiteLog.DateTime < CONVERT(DATETIME,CONVERT(Varchar,Getdate()+1,102)) )
GROUP BY dnn_Portals.PortalName, dnn_SiteLog.DateTime, dnn_Users.Username, dnn_Users.FirstName, dnn_Users.LastName, dnn_Tabs.TabName
0
 

Expert Comment

by:xpert31415
ID: 20424817
SELECT Today=GETDATE()

returns

Today
---------------------------
2007-12-06 20:07:19.957

0
 

Author Closing Comment

by:dstjohnjr
ID: 31413305
That did it.  Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TSQL query to generate xml 4 46
Have a conversion issue with varchar to int in a SQL: Query. 1 40
Syntax for query to update table 2 29
average of calculation (TSQL) 4 26
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question