You can convert it to a date/datetime with

CONVERT(date, '12/31/24', 1)

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

Correct.  However (IT DEPENDS), there are times when a variable will not be updated using ISNULL.  That is why it is sometimes necessary to use the other syntax.  In the...

• This reply was modified 8 months, 3 weeks ago by DesNorton.

I generally use the 2nd version of...

Duplicate post

I would look at "forcing" the overlap.  This can be achieved by applying an extra CTE at the start of the above code

 WITH ForcedOverlap AS (
...

• This reply was modified 9 months, 2 weeks ago by DesNorton.

You dont show your expected output.

Maybe this will point you in the right direction

WITH cteLag AS (
    SELECT  lt.Number
    ...

This code illustrates how AT TIME ZONE automatically caters for DST

DECLARE @Data table (
  MyTimeUTC datetime
);

INSERT INTO @Data ( MyTimeUTC )
VALUES ( '2024-05-01 00:31:15' )
 ...

I am having difficulty understanding what you are trying to achieve.  Maybe some sample data and expected results will help.

That said, I suspect that your design is going to fall...

Always store your datetimes as UTC

The DB creates the timestamp in UTC

The when selecting,

SELECT LocalTime = TimeFromtable AT TIME ZONE 'UTC' AT TIME ZONE 'your clients timezone'
FROM...

This is an alternate piece of code that will match your 2nd attempt

SELECT [IN_1_NOT_IN_2] = s1.SYMBOL
     , [IN_2_NOT_IN_1] = s2.SYMBOL
FROM (
  ...

You can write this as a cross-tab query

SELECT  Q1 = pid
      , Q2 = MAX(IIF(mne = 'Q2', mneval, null))
  ...

This is probably Microsoft pushing back to try and force JEFF to move from datetime to datetime2 😉

It doesnt matter what query you run.  The 16 threads are hitting the same table, joining back to itself 8 times.  There will always be contention.

Bottom line - The more...

If all of the self-joins are needed, some filtered indexes might also help.

As always .... TEST, TEST, TEST

CREATE NONCLUSTERED INDEX ix_Design_Parts_BOMitem
ON design (design, designAlternative, version, class, operation,...

I would try to push my predicates up as high as possible, then isolate my top-level values, before getting the rest of the data.

Something like this

;WITH cteVersion...