0

I'm changing the way our system works to show our employee work orders.

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.

But we are changing our Start Date and End date to also show 'time'.

I am also converting the Start Date and End Date to be in central time. It is inserted to the database as UTC.

In my sql query, the WHERE function shows

dbo.WorkOrders.StartDate <= convert(varchar(10), @current_cst, 102) and dbo.WorkOrders.EndDate >= convert(varchar(10), @current_cst, 102)
and dbo.WorkOrderAssignedEmployee.EmployeeID = @EmployeeID

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.

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?)

If I take the same code and do '= convert' instead of <= convert, it gives me all previous days weather it has a time or not.

I just need today's work on todays date ignoring time completely.

Here's my entire query as it sits now broken because of the introduction of 'time'.

@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 <= convert(varchar(10), @current_cst, 102) and dbo.WorkOrders.EndDate >= convert(varchar(10), @current_cst, 102) and

dbo.WorkOrderAssignedEmployee.EmployeeID = @EmployeeID

order by CONVERT(time, dbo.WorkOrders.StartDate) asc


End

I'm hoping I'm explaining this correctly. Short version. I want to only see work orders that have todays date regardless of their time value.

Aakburns
  • 307
  • 1
  • 5
  • 19
  • 1
    You can. 1. Convert your datetimes to dates. `WHERE dbo.WorkOrders.StartDate <=` to `WHERE CONVERT(date, dbo.WorkOrders.StartDate) <= ` Not great on performance but without restructuring your query... Performance will depend on how many rows. 2. Set the Compare datetime to 00:00:00 tomorrow and then use a less than. Here's an old nugget on the topic from 13 years ago. https://stackoverflow.com/questions/467103/ms-sql-date-only-without-time – MrC aka Shaun Curtis Jul 10 '22 at 17:32
  • @MrCakaShaunCurtis - That's a nonsargable query, and is usually avoidable. – Matt Johnson-Pint Jul 10 '22 at 17:48
  • 1
    @Aakburns - why are you converting to `varchar(10)` ? What does the table schema look like? In general, dates and times should not be stored as `varchar`. – Matt Johnson-Pint Jul 10 '22 at 17:50
  • 1
    Also, your current code has a possible bug in that it is assuming the server's time zone is UTC. If it's anything else, you will have the wrong CST time. You could avoid that by simplifying: `SET @current_cst = sysdatetimeoffset() AT TIME ZONE 'Central Standard Time'` – Matt Johnson-Pint Jul 10 '22 at 17:53
  • Thank you to all who commented here. This solved all of the issues I was having. It was the last piece of the puzzle. Everything else that had to change with this was already in play. Now to testing for a day before release. Also, I'm not sure why we had varchar(10) in there. Our dates are all datetime2(0). Further I made the change for SET @current_cst = sysdatetimeoffset() AT TIME ZONE 'Central Standard Time' as suggested. This is also working correctly. Thanks again. – Aakburns Jul 11 '22 at 02:48

0 Answers0