title: SQL Show only where todays date is today (regardless of time in the date/time value)
body: <p>I'm changing the way our system works to show our employee work orders.</p>
  <p>Previously, Start Date and End Date would show as (example) 2022-07-09 00:00:00.0000000.
  The time would always be 00:00:00.0000000.</p>
  <p>But we are changing our Start Date and End date to also show 'time'.</p>
  <p>I am also converting the Start Date and End Date to be in central time.  It is inserted to the database as UTC.</p>
  <p>In my sql query, the WHERE function shows</p>
  <pre><code>dbo.WorkOrders.StartDate &lt;= convert(varchar(10), @current_cst, 102) and dbo.WorkOrders.EndDate &gt;= convert(varchar(10), @current_cst, 102)
  and dbo.WorkOrderAssignedEmployee.EmployeeID = @EmployeeID
  </code></pre>
  <p>This works as long as the date has no time value and is set as 00:00:00.0000000.
  Introducing a 'time' into the field breaks this and no longer shows todays work.</p>
  <p>For example, if I set StartDate as '2022-07-09 08:00:00.0000000'  This would be today's date at 8AM rather than just todays date with a time of '00:00:00.0000000'(midnight?)</p>
  <p>If I take the same code and do '= convert' instead of &lt;= convert, it gives me all previous days weather it has a time or not.</p>
  <p>I just need today's work on todays date ignoring time completely.</p>
  <p>Here's my entire query as it sits now broken because of the introduction of 'time'.</p>
  <pre><code>@EmployeeID nvarchar(255)
  
  
  AS
  begin
  
  declare @current_cst datetimeoffset;
  set @current_cst = (SELECT getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time')
  
  
  SELECT  
          dbo.WorkOrders.WorkOrderID,
          dbo.WorkOrders.WorkOrderProjectID,
          dbo.WorkOrders.WorkToBeCompleted,
          dbo.WorkOrders.Status,
          dbo.WorkOrders.StartTime,
          dbo.WorkOrders.StartDate,
          dbo.WorkOrders.EndDate,
          dbo.WorkOrders.CompletedDate,
          dbo.WorkOrders.Notes,
          dbo.WorkOrders.JobTypeID,
          dbo.WorkOrders.CreatedBy,
          dbo.WorkOrders.DateCreated,
          dbo.WorkOrders.WorkToBeCompleted,
          dbo.WorkOrderAssignedEmployee.EmployeeID,
          dbo.Projects.ProjectName,
          dbo.Customers.CustomerName,
          dbo.JobType.JobTypeDescription
  
  
  
  
  
  FROM dbo.WorkOrders
          INNER JOIN dbo.JobType ON dbo.WorkOrders.JobTypeID = dbo.JobType.JobTypeID
          --INNER JOIN dbo.WorkOrderEmployeeTimeTracker ON dbo.WorkOrderEmployeeTimeTracker.WorkOrderID = dbo.WorkOrders.WorkOrderID
          INNER JOIN dbo.WorkOrderProjects ON dbo.WorkOrders.WorkOrderProjectID = dbo.WorkOrderProjects.WorkOrderProjectID
          INNER JOIN dbo.Projects ON dbo.WorkOrderProjects.ProjectID = dbo.Projects.ProjectID
          INNER JOIN dbo.Customers ON dbo.Customers.CustomerID = dbo.Projects.CustomerID
          INNER JOIN dbo.WorkOrderAssignedEmployee ON dbo.WorkOrders.WorkOrderID = dbo.WorkOrderAssignedEmployee.WorkOrderID
  --WHERE EmployeeID = @EmployeeID
  WHERE
  dbo.WorkOrders.StartDate &lt;= convert(varchar(10), @current_cst, 102) and dbo.WorkOrders.EndDate &gt;= convert(varchar(10), @current_cst, 102) and
  
  dbo.WorkOrderAssignedEmployee.EmployeeID = @EmployeeID
  
  order by CONVERT(time, dbo.WorkOrders.StartDate) asc
  
  
  End
  </code></pre>
  <p>I'm hoping I'm explaining this correctly.  <strong>Short version.  I want to only see work orders that have todays date regardless of their time value.</strong></p>